Обсуждение: Re: Why data returned inside parentheses in for loop

wujee wrote
> Dear all, 
> 
>  I have a simple query but the data returns inside parentheses, I think
> there may be parameters that control this behavior but not sure what it
> is. 
> 
>  This is what I Have: 
>  begin 
>      for i in (select salary from salaries where employee_id > 1001) loop 
>          dbms_output.put_line('i value: '||i); 
>      end loop; 
>  end; 
> 
> 
>  And I got the result as the followings: 
>  i value: (375402) 
>  i value: (37539) 
>  i value: (375394) 
> 
> 
>  I do not want "(" and ")" in between the values, how should I do that? 
> 
> 
> Thanks,
> Tina

dbms_output.put_line('i value: ' || i.salary)

"i" is a record type that has a single "salary" column.  If it had two
columns, say "SELECT name, salary FROM ..." you would have gotten (correct
quoting not withstanding):

i value: ('Name', 375402)

pl/pgsql does not automatically reduce a single column to be a simple scalar
- the "for rec_var in (query)" always results in rec_var being of type
"record".

David J.






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

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.

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.