Date: Mon, 29 Sep 2014 14:18:23 -0700 (PDT)
From: David G Johnston <david.g.johnston@gmail.com>
To: pgsql-sql@postgresql.org
Message-ID: <1412025503538-5821013.post@n5.nabble.com>
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>
Subject: Re: Why data returned inside parentheses in for loop
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

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.


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql