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****@sanchar net.in> wrote in message
news:3F******** ******@sancharn et.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%ROW TYPE 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