Обсуждение: Parser does not like %ROWTYPE in the RETURNS clause of a function declaration (BUG?)

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>

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

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

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
>

"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