Thread: Parser does not like %ROWTYPE in the RETURNS clause of a function declaration (BUG?)

Parser does not like %ROWTYPE in the RETURNS clause of a function declaration (BUG?)

From

"ezra epstein"

Date:

24 December 2003, 15:47:54

Aother head banger for me.

Below is a complete example of the code

Using Postgres 7.4,
  the function "test" gets this:   psql:temp3.sql:10: ERROR:  syntax error
at or near "%" at character 135
  the function "test2" gets this:  psql:temp3.sql:10: ERROR:  syntax error
at or near "ROWTYPE" at character 141

Very odd.  The first doesn't even like the '%' character -- perhaps because
doof is a table type rather than a column (domain) type???

And when we schema qualify the name of the table then the % is ok, but
ROWTYPE is not.

Is this a well-known limitation or a new (7.4) bug?   I tried combing the
docs to no avail.

Thanks,

   Ezra E.

<code>
/*
 CREATE TABLE doof ( "pk_id"         serial )
  WITHOUT OIDS;
*/

CREATE OR REPLACE FUNCTION test(INTEGER)
    RETURNS doof%ROWTYPE AS '
SELECT * FROM doof WHERE pk_id=$1;
 ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION test2(INTEGER)
    RETURNS public.doof%ROWTYPE AS '
SELECT * FROM doof WHERE pk_id=$1;
 ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
 </code>

Re: Parser does not like %ROWTYPE in the RETURNS clause

From

Ron St-Pierre

Date:

24 December 2003, 16:42:35

ezra epstein wrote:

>Aother head banger for me.
>
>Below is a complete example of the code
>
>Using Postgres 7.4,
>  the function "test" gets this:   psql:temp3.sql:10: ERROR:  syntax error
>at or near "%" at character 135
>  the function "test2" gets this:  psql:temp3.sql:10: ERROR:  syntax error
>at or near "ROWTYPE" at character 141
>
>Very odd.  The first doesn't even like the '%' character -- perhaps because
>doof is a table type rather than a column (domain) type???
>
>And when we schema qualify the name of the table then the % is ok, but
>ROWTYPE is not.
>
>Is this a well-known limitation or a new (7.4) bug?   I tried combing the
>docs to no avail.
>
>Thanks,
>
>   Ezra E.
>
><code>
>/*
> CREATE TABLE doof ( "pk_id"         serial )
>  WITHOUT OIDS;
>*/
>
>CREATE OR REPLACE FUNCTION test(INTEGER)
>    RETURNS doof%ROWTYPE AS '
>SELECT * FROM doof WHERE pk_id=$1;
> ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
>
>CREATE OR REPLACE FUNCTION test2(INTEGER)
>    RETURNS public.doof%ROWTYPE AS '
>SELECT * FROM doof WHERE pk_id=$1;
> ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
> </code>
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
>
>
>
Try replacing the rowtype with SETOF doof:

CREATE OR REPLACE FUNCTION test(INTEGER)
    RETURNS SETOF doof AS '
SELECT * FROM doof WHERE pk_id=$1;
 ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;

Hope that helps.
Ron

Re: Parser does not like %ROWTYPE in the RETURNS clause of a

From

Sai Hertz And Control Systems

Date:

24 December 2003, 16:54:13

Dear ezra epstein ;

>Using Postgres 7.4,
>  the function "test" gets this:   psql:temp3.sql:10: ERROR:  syntax error
>at or near "%" at character 135
>  the function "test2" gets this:  psql:temp3.sql:10: ERROR:  syntax error
>at or near "ROWTYPE" at character 141
>
>Very odd.  The first doesn't even like the '%' character -- perhaps because
>doof is a table type rather than a column (domain) type???
>
>
ROWTYPE  for SQL Language ???? you may please check that

><code>
>/*
> CREATE TABLE doof ( "pk_id"         serial )
>  WITHOUT OIDS;
>*/
>
>CREATE OR REPLACE FUNCTION test(INTEGER)
>    RETURNS doof%ROWTYPE AS '
>SELECT * FROM doof WHERE pk_id=$1;
> ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
>
>CREATE OR REPLACE FUNCTION test2(INTEGER)
>    RETURNS public.doof%ROWTYPE AS '
>SELECT * FROM doof WHERE pk_id=$1;
> ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
> </code>
>
>
The above code gave error on mine  system also PostgreSQL 7.3.4
what I think you want to  something like this
<code>

CREATE OR REPLACE FUNCTION test2(INTEGER)
RETURNS public.doof AS '
SELECT * FROM doof WHERE pk_id = $1;
 ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;

CREATE OR REPLACE FUNCTION test1(INTEGER)
RETURNS doof AS '
SELECT * FROM doof WHERE pk_id = $1;
 ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
</code>
Mine Limited knowledge tells me that this is not a BUG but just an
effect of thinking  out of the box
Shoot back if I was right please.
Regards,
Vishal Kashyap

Re: Parser does not like %ROWTYPE in the RETURNS clause of a

From

"ezra epstein"

Date:

24 December 2003, 23:53:00

Thanks very much for the reply(s).

    This does work!  I'm not surprised that it does (after more reading of
docs).  What surprises me is that %ROWTYPE does not work as it seems to work
most other places.  I'm not enough of an Oracle PL/SQL whiz to know if
%ROWTYPE(s) can be returned from Oracle functions.  If not, then this makes
some sense.
    Still, for consistency, it seems, IMHO -- and from my limited knowledge
of Postgres -- that consistent declarations would be desirable.  So if we
can:

DECLARE
    result       doof%ROWTYPE
BEGIN
   ....

and we can, Then it seems consistent and sensible to allow the %ROWTYPE form
for declaring a return type.

   As to the other post suggesting returning a SETOF -- that will work, but
it is not what I want.  I really just want a single row (a tuple) not
multiple rows.  So declaring SETOF would be the wrong return type.

Thanks for the replies.

 == Ezra Epstein



"Sai Hertz And Control Systems" <sank89@sancharnet.in> wrote in message
news:3FE9D27C.3020307@sancharnet.in...
> Dear ezra epstein ;
>
> >Using Postgres 7.4,
> >  the function "test" gets this:   psql:temp3.sql:10: ERROR:  syntax
error
> >at or near "%" at character 135
> >  the function "test2" gets this:  psql:temp3.sql:10: ERROR:  syntax
error
> >at or near "ROWTYPE" at character 141
> >
> >Very odd.  The first doesn't even like the '%' character -- perhaps
because
> >doof is a table type rather than a column (domain) type???
> >
> >
> ROWTYPE  for SQL Language ???? you may please check that
>
> ><code>
> >/*
> > CREATE TABLE doof ( "pk_id"         serial )
> >  WITHOUT OIDS;
> >*/
> >
> >CREATE OR REPLACE FUNCTION test(INTEGER)
> >    RETURNS doof%ROWTYPE AS '
> >SELECT * FROM doof WHERE pk_id=$1;
> > ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
> >
> >CREATE OR REPLACE FUNCTION test2(INTEGER)
> >    RETURNS public.doof%ROWTYPE AS '
> >SELECT * FROM doof WHERE pk_id=$1;
> > ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
> > </code>
> >
> >
> The above code gave error on mine  system also PostgreSQL 7.3.4
> what I think you want to  something like this
> <code>
>
> CREATE OR REPLACE FUNCTION test2(INTEGER)
> RETURNS public.doof AS '
> SELECT * FROM doof WHERE pk_id = $1;
>  ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
>
> CREATE OR REPLACE FUNCTION test1(INTEGER)
> RETURNS doof AS '
> SELECT * FROM doof WHERE pk_id = $1;
>  ' LANGUAGE SQL STABLE RETURNS NULL ON NULL INPUT;
> </code>
> Mine Limited knowledge tells me that this is not a BUG but just an
> effect of thinking  out of the box
> Shoot back if I was right please.
> Regards,
> Vishal Kashyap
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>                http://archives.postgresql.org
>

Re: Parser does not like %ROWTYPE in the RETURNS clause of a function declaration (BUG?)

From

Tom Lane

Date:

25 December 2003, 15:12:07

"ezra epstein" <ee_newsgroup_post@prajnait.com> writes:
> CREATE OR REPLACE FUNCTION test(INTEGER)
>     RETURNS doof%ROWTYPE AS '

As somebody else pointed out, just write "doof" and you are done.
%ROWTYPE is an Oracle-ism that we support in the bodies of plpgsql
functions for compatibility's sake, but not elsewhere.

BTW, there is a related notation that we do support in CREATE FUNCTION
argument and result type declarations:
    table.field % TYPE
for naming a type by reference to a field that has that type.

            regards, tom lane