Обсуждение: syntax question
FOR total IN EXECUTE subquery LOOP
END LOOP;RETURN total.tot;
I have a function that returns a total from a dynamic query I use to generate the total query
I've tried to modify it as a loop to get a single row value is unnecessary but I'm failing on my syntax
I've tried
select into total subquery
-but it wont work(when I attempt this I remove the select from the beginning of my sub query)
can someone show me the syntax to execute a select into with a string that contains a query in it?
James
James, > FOR total IN EXECUTE subquery LOOP > > END LOOP; > > RETURN total.tot; The above is fine, except that you need to assign total.tot to a variable *inside* the loop: FOR total IN EXECUTE subquery LOOPreturn_total := total.tot; END LOOP; RETURN return_total; This is because the expression "total.tot" is out of scope as soon as the loop exits. -- Josh Berkus Aglio Database Solutions San Francisco
James, > I understand this and my current example actually works. > My question is I dont need a loop as it only ever returns one row. This is a current limitation of PL/pgSQL. Until PL/pgSQL is improved by some enterprising soul, you cannot select the results of a query directly into a RECORD variable without a loop. For that matter, you will find that you cannot declare an array variable inside a PL/pgSQL procedure. This is also on the TODO list. -- Josh Berkus Aglio Database Solutions San Francisco
James, > but thats what: > rec record > select into rec id from table; > return rec.id > > does > > my question was can i do this with a query built inside a string? No. That's what I was talking about. You have to use the loop. -- Josh Berkus Aglio Database Solutions San Francisco
--- Josh Berkus <josh@agliodbs.com> wrote:
> you cannot select the results of
> a query directly into a
> RECORD variable without a loop.
>
Pardon??
kper=# create function test(integer) returns varchar
as 'declare stuff record; begin select * into stuff
from employee where empid =$1; return stuff.empnum;
end;' language 'plpgsql';
CREATE
kper=# select test(66664);test
------3094
(1 row)
kper=# select empid, empnum from employee where empid
= 66664;empid | empnum
-------+--------66664 | 3094
(1 row)
This functinality has been in pl/pgsql from early days
AFAIK. Be aware that this only works reliably if you
have some way to be sure of what will be selected
("empid" is a unique column in this example), because
the second and later rows to be returned from the
query will be silently discarded.
__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - forms, calculators, tips, more
http://taxes.yahoo.com/
Jeff Eckermann <jeff_eckermann@yahoo.com> writes:
> --- Josh Berkus <josh@agliodbs.com> wrote:
>> you cannot select the results of
>> a query directly into a
>> RECORD variable without a loop.
> Pardon??
I think Josh meant to say you can't select the results of a *dynamically
constructed* query without a loop --- that is, you need FOR ... EXECUTE.
A plain EXECUTE doesn't support plpgsql's notion of SELECT INTO.
regards, tom lane
Jeff, > I think Josh meant to say you can't select the results of a *dynamically > constructed* query without a loop --- that is, you need FOR ... EXECUTE. > A plain EXECUTE doesn't support plpgsql's notion of SELECT INTO. That's correct. See the rest of the thread. -- Josh Berkus Aglio Database Solutions San Francisco