Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause
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. /* 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; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] 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 [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] Parser does not like %ROWTYPE in the RETURNS clause of a
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 /* 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; The above code gave error on mine system also PostgreSQL 7.3.4 what I think you want to something like this 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; 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
[GENERAL] 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. /* 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; ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
