Re: Why data returned inside parentheses in for loop

David G Johnston wrote
> 
> wujee wrote
>> Thanks David for your reply.  If the result is being a "record" type, how
>> do we getting a list of data as text and input to other query, for
>> example I have the following code, how would I go by doing it?
>> 
>>  declare
>>    v_list text;
>>  begin
>>      for i in (select emp_id from employees where emp_id in (select
>> emp_id from salaries where salary > 3000) loop
>>          v_list :=''''||i||''','||v_list;
>>      delete from salaries where salary > 3000;
>>          delete from employees where emp_id in (v_list);
>>      end loop;
>>  end; 
> Using my example on how to print just the value of salary you should be
> able to figure this out.
> 
> That said, your example code is, to put it bluntly, stupid.
> 
> Even if you were to build v_list incrementally like this having the delete
> statements inside the loop means you will keep executing them.  At minimum
> you'd simply build the v_list and execute the delete commands after the
> loop has ended.
> 
> However, there is no reason to add a loop here in the first place.  The
> salaries delete can simply be executed and the employees delete can use
> the loop query directly in its where clause.
> 
> I'd also write the for query as: "SELECT DISTINCT emp_id FROM salaries
> ..." - though depending on whether salaries-employee is 1-to-1 or
> 1-to-many the DISTINCT would be redundant.  If it is 1-to-many then
> DISTINCT would be needed but I would have to assume you are missing the
> part of the where clause that allows you to distinguish between different
> salaries for the same employee.
> 
> David J.

You may also want to lookup FOREIGN KEY and ON DELETE CASCADE

David J.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Why-data-returned-inside-parentheses-in-for-loop-tp5820980p5821013.html
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.