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

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

"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