Thread: 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 <survivedsushi@yahoo.com> 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;
>
>
>
>
>  ________________________________
>  Yahoo! DSL Something to write home about. Just $16.99/mo. or less
>
>

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

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

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