469,945 Members | 1,447 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,945 developers. It's quick & easy.

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>
Nov 12 '05 #1
4 6880
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 ma*******@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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ma*******@postgresql.org so that your
message can get through to the mailing list cleanly

Nov 12 '05 #2
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

Nov 12 '05 #3
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" <sa****@sancharnet.in> wrote in message
news:3F**************@sancharnet.in...
Dear ezra epstein ;
Using Postgres 7.4,
the function "test" gets this: psql:temp3.sql:10: ERROR: syntax errorat or near "%" at character 135
the function "test2" gets this: psql:temp3.sql:10: ERROR: syntax errorat or near "ROWTYPE" at character 141

Very odd. The first doesn't even like the '%' character -- perhaps becausedoof 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

Nov 12 '05 #4
"ezra epstein" <ee***************@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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by Phil Reardon | last post: by
6 posts views Thread by feminine.aura | last post: by
4 posts views Thread by werasm | last post: by
1 post views Thread by INeedADip | last post: by
13 posts views Thread by Sri Harsha Dandibhotla | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.