agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Re: Why data returned inside parentheses in for loop
Date: Mon, 29 Sep 2014 14:18:23 -0700 (PDT)
Message-ID: <1412025503538-5821013.post@n5.nabble.com> (raw)
In-Reply-To: <1412025405544-5821012.post@n5.nabble.com>
References: <1412018553142-5820980.post@n5.nabble.com>
<1412019286556-5820984.post@n5.nabble.com>
<1412024156842-5821009.post@n5.nabble.com>
<1412025405544-5821012.post@n5.nabble.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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-tp5820980p5...
Sent from the PostgreSQL - sql mailing list archive at Nabble.com.
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
view thread (3+ messages)
Message-ID: <1412025503538-5821013.post@n5.nabble.com>
Permalink: ../1412025503538-5821013.post@n5.nabble.com/
Also on: postgresql.org/message-id/1412025503538-5821013.post@n5.nabble.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: david.g.johnston@gmail.com
Subject: Re: Why data returned inside parentheses in for loop
In-Reply-To: <1412025503538-5821013.post@n5.nabble.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox