pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedsyntax question
7+ messages / 4 participants
[nested] [flat]
* syntax question
@ 2003-02-23 22:07 James Cooper <jim@luckydigital.com>
0 siblings, 1 reply; 7+ messages in thread
From: James Cooper @ 2003-02-23 22:07 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax question
@ 2003-02-23 22:52 Josh Berkus <josh@agliodbs.com>
parent: James Cooper <jim@luckydigital.com>
0 siblings, 1 reply; 7+ messages in thread
From: Josh Berkus @ 2003-02-23 22:52 UTC (permalink / raw)
To: James Cooper <jim@luckydigital.com>; pgsql-sql
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 LOOP
return_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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax question
@ 2003-02-23 23:07 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
0 siblings, 2 replies; 7+ messages in thread
From: Josh Berkus @ 2003-02-23 23:07 UTC (permalink / raw)
To: James Cooper <jim@luckydigital.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax question
@ 2003-02-24 00:25 Josh Berkus <josh@agliodbs.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2003-02-24 00:25 UTC (permalink / raw)
To: James Cooper <jim@luckydigital.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax question
@ 2003-02-24 15:17 Jeff Eckermann <jeff_eckermann@yahoo.com>
parent: Josh Berkus <josh@agliodbs.com>
1 sibling, 1 reply; 7+ messages in thread
From: Jeff Eckermann @ 2003-02-24 15:17 UTC (permalink / raw)
To: Josh Berkus <josh@agliodbs.com>; James Cooper <jim@luckydigital.com>; +Cc: pgsql-sql
--- 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/
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax question
@ 2003-02-24 15:30 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Eckermann <jeff_eckermann@yahoo.com>
0 siblings, 1 reply; 7+ messages in thread
From: Tom Lane @ 2003-02-24 15:30 UTC (permalink / raw)
To: Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: Josh Berkus <josh@agliodbs.com>; James Cooper <jim@luckydigital.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: syntax question
@ 2003-02-24 18:22 Josh Berkus <josh@agliodbs.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Josh Berkus @ 2003-02-24 18:22 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; Jeff Eckermann <jeff_eckermann@yahoo.com>; +Cc: James Cooper <jim@luckydigital.com>; pgsql-sql
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
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2003-02-24 18:22 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-02-23 22:07 syntax question James Cooper <jim@luckydigital.com>
2003-02-23 22:52 ` Josh Berkus <josh@agliodbs.com>
2003-02-23 23:07 ` Josh Berkus <josh@agliodbs.com>
2003-02-24 00:25 ` Josh Berkus <josh@agliodbs.com>
2003-02-24 15:17 ` Jeff Eckermann <jeff_eckermann@yahoo.com>
2003-02-24 15:30 ` Tom Lane <tgl@sss.pgh.pa.us>
2003-02-24 18:22 ` Josh Berkus <josh@agliodbs.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox