From: | "ezra epstein" <ee_newsgroup_post(at)prajnait(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Parser does not like %ROWTYPE in the RETURNS clause of a |
Date: | 2003-12-25 00:06:37 |
Message-ID: | rIKdnS5l8saYu3eiXTWc-w@speakeasy.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)sancharnet(dot)in> wrote in message
news:3FE9D27C(dot)3020307(at)sancharnet(dot)in(dot)(dot)(dot)
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | ezra epstein | 2003-12-25 00:10:25 | Re: Where do I get Windows libpq and header files? |
Previous Message | ezra epstein | 2003-12-24 23:59:43 | Re: SELECT INTO broken (?) in PL/pgSQL when using a set returning function in FROM clause (BGUG?) |