Postgres Pro
Downloads
Home   >   mailing lists

Thread: syntax question

syntax question

From
"James Cooper"
Date:

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

Re: syntax question

From
Josh Berkus
Date:
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

Re: syntax question

From
Josh Berkus
Date:
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

Re: syntax question

From
Josh Berkus
Date:
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

Re: syntax question

From
Jeff Eckermann
Date:
--- 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/

Re: syntax question

From
Tom Lane
Date:
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

Re: syntax question

From
Josh Berkus
Date:
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