plpgsql question

Assuming records is the name of a table...

create or replace function getrecord(int,text) RETURNS SETOF records as $$
DECLARE
row records%rowtype;
BEGIN
FOR row IN SELECT * FROM my_tbl
WHERE ...
LOOP
RETURN NEXT row;
END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql;

On 1/5/06, Matthew Peter wrote:

I'm trying to do a simple SELECT * in plpgsql that returns a set of records
as a row w/ columns, not a row into a variable, w/ some conditionals.

The function below is semi-pseudo with what I'm trying to... If anyone
could give me an example that works by returning it as a resultset
maintaining the columns, that would be awesome and I could take it from
there.

I've read the pl/pgsql section of the docs and the Douglas book but I'm
still confused on this issue...

Thanks

create or replace function getrecord(int,text) RETURNS SETOF records as $$
DECLARE
-- event := rows to return from the table below

BEGIN
event := SELECT * FROM my_tbl
WHERE 1 = 1
and my_tbl_id IN (0$1) ||
' IF $2 IS NOT NULL THEN' || and username = $2 || 'END IF;'
; -- end sql statement

RETURN event;

END;
$$ LANGUAGE plpgsql;

Pandurangan R S <pandurangan.r.s@gmail.com> wrote: Assuming records is the name of a table...

create or replace function getrecord(int,text) RETURNS SETOF records as $$
DECLARE
row records%rowtype;
BEGIN
FOR row IN SELECT * FROM my_tbl
WHERE ...
LOOP
RETURN NEXT row;
END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql;

Thanks for the reply.

Is it possible to skip the loop and just return all records in a single query and shove all those rows into a table variable?

Also, the WHERE part is also important cause I'm not sure i got that part right? Would this call for EXECUTE or will it be okay and be planned the first time by the query planner?

---------------------------------
Yahoo! Photos
Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever.

On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:

Is it possible to skip the loop and just return all records in a
single query and shove all those rows into a table variable?

Not in PL/pgSQL -- you need to return each row with RETURN NEXT,
generally from within a loop. Why do you want to avoid that?

For simple functions you could use SQL instead of PL/pgSQL:

http://www.postgresql.org/docs/8.1/interactive/xfunc-sql.html#AEN31627

Also, the WHERE part is also important cause I'm not sure i got
that part right? Would this call for EXECUTE or will it be okay and
be planned the first time by the query planner?

If each call to the function issues the same query, just with
different values, then you shouldn't need to use EXECUTE. If the
query differs depending on the function parameters then you have
several possibilities:

* You could build the query string and use EXECUTE. Be sure to
read about quote_literal() and quote_ident().

* You could use an IF statement to execute the query you need.

* You could put the queries in separate functions. You can use
the same name for different functions if their call signatures
are different, e.g., getrecord(integer) and getrecord(integer, text).

* You could rewrite the query, possibly using CASE or COALESCE
to handle NULL values.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> wrote: On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:

Is it possible to skip the loop and just return all records in a
single query and shove all those rows into a table variable?

Not in PL/pgSQL -- you need to return each row with RETURN NEXT,
generally from within a loop. Why do you want to avoid that?
I was thinking it would be more efficient to pull all the records in one call rather than 50 calls. For all I know it probably executes 50 calls in the internals when translating the IN (IDs).
* You could use an IF statement to execute the query you need.
That's what I was trying to do, but I'm not sure i was doing it in the right context, since it was IN the query, not testing after it. Figured I'd ask the list if I was trying something impossible or if I was close to help get me on track.
* You could put the queries in separate functions.
The query is so similiar (occasionally match on extra WHERE arg) it would be nice just to use a conditional to match if that extra argument is given as not null...rather than maintain two simliar functions if possible, while keeping it planned after the first run.

Does using an IF predicate in the WHERE in the SQL call require EXECUTE since (I guess) I'm making the SQL statement somewhat dynamic? All I've been able to find is IF handling after the query, not in it.

Thanks again

Matt

---------------------------------
Yahoo! DSL Something to write home about. Just $16.99/mo. or less

On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:

Michael Fuhr <mike@fuhr.org> wrote:

On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:

Is it possible to skip the loop and just return all records in a
single query and shove all those rows into a table variable?

Not in PL/pgSQL -- you need to return each row with RETURN NEXT,
generally from within a loop. Why do you want to avoid that?

I was thinking it would be more efficient to pull all the records in
one call rather than 50 calls. For all I know it probably executes 50
calls in the internals when translating the IN (IDs).

I wouldn't worry about that unless you can demonstrate that it's
causing a performance problem. Even then you're stuck because
that's how set-returning functions work.

* You could use an IF statement to execute the query you need.

That's what I was trying to do, but I'm not sure i was doing it in
the right context, since it was IN the query, not testing after it.
Figured I'd ask the list if I was trying something impossible or if
I was close to help get me on track.

The IF statement needs to be part of the PL/pgSQL logic, not part
of the query string. However, you might be able to use CASE or
COALESCE in the query, as in

WHERE my_tbl_id = $1
AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END

or

WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)

or

WHERE my_tbl_id = $1 AND COALESCE($2, username) = username

With predicates such as these you wouldn't need to use EXECUTE and
you could write the query only once.

--
Michael Fuhr

Michael Fuhr <mike@fuhr.org> wrote: On Fri, Jan 06, 2006 at 01:14:38AM -0800, Matthew Peter wrote:

Michael Fuhr wrote:

On Thu, Jan 05, 2006 at 12:50:34AM -0800, Matthew Peter wrote:

Is it possible to skip the loop and just return all records in a
single query and shove all those rows into a table variable?

Not in PL/pgSQL -- you need to return each row with RETURN NEXT,
generally from within a loop. Why do you want to avoid that?

I was thinking it would be more efficient to pull all the records in
one call rather than 50 calls. For all I know it probably executes 50
calls in the internals when translating the IN (IDs).

I wouldn't worry about that unless you can demonstrate that it's
causing a performance problem. Even then you're stuck because
that's how set-returning functions work.

* You could use an IF statement to execute the query you need.

That's what I was trying to do, but I'm not sure i was doing it in
the right context, since it was IN the query, not testing after it.
Figured I'd ask the list if I was trying something impossible or if
I was close to help get me on track.

The IF statement needs to be part of the PL/pgSQL logic, not part
of the query string. However, you might be able to use CASE or
COALESCE in the query, as in

WHERE my_tbl_id = $1
AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END

or

WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)

or

WHERE my_tbl_id = $1 AND COALESCE($2, username) = username

With predicates such as these you wouldn't need to use EXECUTE and
you could write the query only once.

--
Michael Fuhr

I'll try that out tomorrow. Thanks Micheal

---------------------------------
Yahoo! DSL Something to write home about. Just $16.99/mo. or less

snip
WHERE my_tbl_id = $1
AND CASE WHEN $2 IS NULL THEN TRUE ELSE $2 = username END

or

WHERE my_tbl_id = $1 AND COALESCE($2 = username, TRUE)

or

WHERE my_tbl_id = $1 AND COALESCE($2, username) = username

With predicates such as these you wouldn't need to use EXECUTE and
you could write the query only once.
That did work. Thanks.

One other quick question, (figure it still applies to the subject line :) when returning a row from a function I'm trying to include an aggregate, but it's not showing up in the query result and I think it's because it's not included in the RETURN NEXT row;? How do I return it as part of the resultset...

create or replace function getrecord(int,text) RETURNS SETOF my_tbl as $$
DECLARE
row my_tbl%rowtype;

BEGIN
FOR row IN SELECT *, SUBSTR(title,1,25) as short_title FROM my_tbl
WHERE ...
LOOP
RETURN NEXT row;
END LOOP;

RETURN;

END;
$$ LANGUAGE plpgsql;
Thanks

---------------------------------
Yahoo! Photos
Got holiday prints? See all the ways to get quality prints in your hands ASAP.

On Mon, Jan 09, 2006 at 01:01:33PM -0800, Matthew Peter wrote:

One other quick question, (figure it still applies to the subject
line :) when returning a row from a function I'm trying to include an
aggregate, but it's not showing up in the query result and I think
it's because it's not included in the RETURN NEXT row;? How do I
return it as part of the resultset...

Terminology point: you used the word "aggregate" but the function
below doesn't have an aggregate. Aggregates are functions that
operate on multiple rows, like count() and sum(); substr() doesn't
do that so it's not an aggregate.

create or replace function getrecord(int,text) RETURNS SETOF my_tbl as $$
DECLARE
row my_tbl%rowtype;

BEGIN
FOR row IN SELECT *, SUBSTR(title,1,25) as short_title FROM my_tbl

[...]

You've declared the row variable to be of type my_tbl so whatever
columns my_tbl has are the columns you get. If you want to return
additional columns then you have a few choices:

1. Create a composite type with the desired columns, declare the
function to return SETOF that type, and declare row to be of
that type.

2. Declare the function to return SETOF record, declare row to
be of type record, and provide a column definition list when
you call the function.

3. Use OUT parameters (new in 8.1).

--
Michael Fuhr

Terminology point: you used the word "aggregate" but the function
below doesn't have an aggregate. Aggregates are functions that
operate on multiple rows, like count() and sum(); substr() doesn't
do that so it's not an aggregate.

ya. my mistake.

[snip]
1. Create a composite type with the desired columns, declare the
function to return SETOF that type, and declare row to be of
that type.
k. this is where i was confused. this is exactly what i wanted/needed

Thanks

---------------------------------
Yahoo! Photos � Showcase holiday pictures in hardcover
Photo Books. You design it and we�ll bind it!