Thread: understanding select into
Hi, I am reviewing a function written by some xTuple guys. What is interesting about it is it uses the "INTO" statement like select something into _p from sometable where somecriteria. The function contiunes and uses the data retreived _p.somefield_name And then the function ends. Ok my question: I also thought the select "into" created a real table. But after running the function the table does not exist. I see no where that a 'drop' is issued. In fact the function uses lot's of select into's like (_test, _r, etc..). So would some kind soul explain what is happening. Could it be that "_p" is drop automaticly when the function ends? Something to do with scope. Could it have something to do with the fact the function returns only an integer? And that causes the table to be drop. As you can see I'm lost here! Johnf
<div class="Section1"><p class="MsoPlainText">Johnf,<p class="MsoPlainText"> <p class="MsoPlainText">I would think that the<b>_p, _test, _r</b> etc are local variables within the procedure/function and this is the way that the value (from theselect) gets assigned to that local variable.<p class="MsoPlainText"> <p class="MsoPlainText">-----Original Message-----<br/> From: pgsql-sql-owner@postgresql.org [mailto:pgsql-sql-owner@postgresql.org] On Behalf Of John<br /> Sent:Friday, April 09, 2010 12:19 PM<br /> To: pgsql-sql@postgresql.org<br /> Subject: [SQL] understanding select into<pclass="MsoPlainText"> <p class="MsoPlainText">Hi,<p class="MsoPlainText">I am reviewing a function written by somexTuple guys. What is interesting <p class="MsoPlainText">about it is it uses the "INTO" statement like<p class="MsoPlainText"> <pclass="MsoPlainText">select something into _p from sometable where somecriteria.<p class="MsoPlainText"> <pclass="MsoPlainText">The function contiunes and uses the data retreived <p class="MsoPlainText">_p.somefield_name<pclass="MsoPlainText"> <p class="MsoPlainText">And then the function ends.<p class="MsoPlainText"> <pclass="MsoPlainText"> <p class="MsoPlainText">Ok my question:<p class="MsoPlainText"> <p class="MsoPlainText">Ialso thought the select "into" created a real table. But after running the <p class="MsoPlainText">functionthe table does not exist. I see no where that a 'drop' is issued. <p class="MsoPlainText">Infact the function uses lot's of select into's like (_test, _r, etc..). So <p class="MsoPlainText">wouldsome kind soul explain what is happening.<p class="MsoPlainText"> <p class="MsoPlainText">Couldit be that "_p" is drop automaticly when the function ends? Something <p class="MsoPlainText">todo with scope.<p class="MsoPlainText"> <p class="MsoPlainText">Could it have something to do withthe fact the function returns only an <p class="MsoPlainText">integer? And that causes the table to be drop.<p class="MsoPlainText"> <pclass="MsoPlainText">As you can see I'm lost here!<p class="MsoPlainText"> <p class="MsoPlainText"> <pclass="MsoPlainText">Johnf<p class="MsoPlainText"> <p class="MsoPlainText">-- <p class="MsoPlainText">Sentvia pgsql-sql mailing list (pgsql-sql@postgresql.org)<p class="MsoPlainText">To make changes toyour subscription:<p class="MsoPlainText">http://www.postgresql.org/mailpref/pgsql-sql</div>
Hello 2010/4/9 John <johnf@jfcomputer.com>: > Hi, > I am reviewing a function written by some xTuple guys. What is interesting > about it is it uses the "INTO" statement like > > select something into _p from sometable where somecriteria. > > The function contiunes and uses the data retreived > _p.somefield_name > > And then the function ends. > > > Ok my question: > > I also thought the select "into" created a real table. But after running the > function the table does not exist. I see no where that a 'drop' is issued. > In fact the function uses lot's of select into's like (_test, _r, etc..). So > would some kind soul explain what is happening. > _p is record variable. See some lines before. There will be DECLARE part DECLARE p RECORD; There is plpgsql's SELECT INTO and SQL's SELECT INTO with little bit different syntax. First - target is list of variables or record variable, second - target is table. > Could it be that "_p" is drop automaticly when the function ends? Something > to do with scope. > _p is just variable regards Pavel Stehule > Could it have something to do with the fact the function returns only an > integer? And that causes the table to be drop. > > As you can see I'm lost here! > > > Johnf > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Wow thanks to all that replied - you folks are correct. The "_p" and the others are vars. _p RECORD; I won't forget that one for a long time (however, I do drink :-)) Johnf On Friday 09 April 2010 10:32:51 am Pavel Stehule wrote: > Hello > > 2010/4/9 John <johnf@jfcomputer.com>: > > Hi, > > I am reviewing a function written by some xTuple guys. What is > > interesting about it is it uses the "INTO" statement like > > > > select something into _p from sometable where somecriteria. > > > > The function contiunes and uses the data retreived > > _p.somefield_name > > > > And then the function ends. > > > > > > Ok my question: > > > > I also thought the select "into" created a real table. But after running > > the function the table does not exist. I see no where that a 'drop' is > > issued. In fact the function uses lot's of select into's like (_test, _r, > > etc..). So would some kind soul explain what is happening. > > _p is record variable. See some lines before. There will be DECLARE part > > DECLARE p RECORD; > > There is plpgsql's SELECT INTO and SQL's SELECT INTO with little bit > different syntax. First - target is list of variables or record > variable, second - target is table. > > > Could it be that "_p" is drop automaticly when the function ends? > > Something to do with scope. > > _p is just variable > > regards > Pavel Stehule > > > Could it have something to do with the fact the function returns only an > > integer? And that causes the table to be drop. > > > > As you can see I'm lost here! > > > > > > Johnf