Re: Why data returned inside parentheses in for loop

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.

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