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.