Thread: Newbie question: returning rowtypes from a plpgsql function

I wrote a function that returns a rowtype.  The rowtype is assigned a
value by a query using SELECT INTO.  The query sometimes will return
no rows.  When it does, the function's return value is a row with no
values.

I would have expected it to return 0 rows, like the query itself.  Am
I doing something wrong or is this the expected behavior?  Is there a
standard way to code around this?  I expected my client code to check
the number of rows returned to decide what to do next.

thanks

Larry White <ljw1001@gmail.com> writes:
> I wrote a function that returns a rowtype.  The rowtype is assigned a
> value by a query using SELECT INTO.  The query sometimes will return
> no rows.  When it does, the function's return value is a row with no
> values.

> I would have expected it to return 0 rows, like the query itself.

How exactly would SELECT INTO return 0 rows?  Perhaps the target
variables vanish into a black hole?

            regards, tom lane

Please excuse my ignorance of databases and black holes.

I don't have access to a Postgres db right now so I tried an
experiment with mysql.  Since they don't have a "select into" that
creates a table, I tried this:

mysql> create table t as ( select * from table_x);

since table_x has no rows, I get:

Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show tables;
| Tables_in_test    |
| table_x    |
| t    |
+----------------------+
2 rows in set (0.02 sec)

So it creates a table called t with no records and the same structure
as table_x. That's what I thought the postgresql SELECT INTO would do.
 Now that I looked at the documentation more closely, I see that
SELECT INTO returns a table when used in a query but an array of
values when used in plpgsql, so that's at least part of what I have
wrong.

Having given it more thought, I think another error was to not declare
the function as returning SETOF, so I can give that a try later.

The declarations section of the pl/pgsql documentation doesn't explain
how to declare a variable to represent a set of rows so if anyone can
suggest something that would be helpful.


Thanks.

On Tue, 30 Nov 2004 22:58:11 -0500, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Larry White <ljw1001@gmail.com> writes:
> > I wrote a function that returns a rowtype.  The rowtype is assigned a
> > value by a query using SELECT INTO.  The query sometimes will return
> > no rows.  When it does, the function's return value is a row with no
> > values.
>
> > I would have expected it to return 0 rows, like the query itself.
>
> How exactly would SELECT INTO return 0 rows?  Perhaps the target
> variables vanish into a black hole?
>
>                         regards, tom lane
>

Larry White <ljw1001@gmail.com> writes:
> mysql> create table t as ( select * from table_x);

That works in Postgres too.  The SELECT INTO construct is a bit broken
since, as you discovered, it has a different meaning in plpgsql than
in the main SQL language.  So I recommend using CREATE TABLE AS when
you want to create a table this way.

> The declarations section of the pl/pgsql documentation doesn't explain
> how to declare a variable to represent a set of rows

You can't.  Possibly a cursor would help?

            regards, tom lane