Thread: procedure properties problem

Hi!
I don't know if this is missing feature or bug, but just to report this.

If I create functions like
create or replace function example(out data varchar) returns setof record as
$$
select data from table
$$ language sql;

pgAdmin displays it without 'returns setof record'.

> -----Original Message-----
> From: pgadmin-support-owner@postgresql.org
> [mailto:pgadmin-support-owner@postgresql.org] On Behalf Of
> Rikard Pavelic
> Sent: 16 January 2006 15:02
> To: pgadmin-support@postgresql.org
> Subject: [pgadmin-support] procedure properties problem
>
> Hi!
> I don't know if this is missing feature or bug, but just to
> report this.
>
> If I create functions like
> create or replace function example(out data varchar) returns
> setof record as
> $$
> select data from table
> $$ language sql;
>
> pgAdmin displays it without 'returns setof record'.

The function written above will never work as you have a mismatch of
parameter and return types, but in answer to your query, the PostgreSQL
docs say:

"When there are OUT or INOUT parameters, the RETURNS clause may be
omitted."

(http://www.postgresql.org/docs/8.1/interactive/sql-createfunction.html)

If you remove the OUT/INOUT parameters, pgAdmin will display the
definition with the RETURNS SETOF clause.

Regards, Dave.

Dave Page wrote:
> The function written above will never work as you have a mismatch of
> parameter and return types, but in answer to your query, the PostgreSQL
> docs say:
>
> "When there are OUT or INOUT parameters, the RETURNS clause may be
> omitted."
>
> (http://www.postgresql.org/docs/8.1/interactive/sql-createfunction.html)
>
> If you remove the OUT/INOUT parameters, pgAdmin will display the
> definition with the RETURNS SETOF clause.
>
> Regards, Dave.
>
>   

Well, look at this example then

create table tabla
(id integer primary key,data varchar
);

create or replace function example(out data varchar) --returns setof varchar
as $$
select data from tabla
$$
language sql;

insert into tabla values(1,'first example');
insert into tabla values(2,'second example');

select * from example();

If I omit the 'returns setof varchar' I will get only one result from select
If I add 'returns setof varchar' I will get two results.


Best regards,      Rikard

> -----Original Message-----
> From: Rikard Pavelic [mailto:rikard.pavelic@zg.htnet.hr]
> Sent: 17 January 2006 10:40
> To: Dave Page
> Cc: pgadmin-support@postgresql.org
> Subject: Re: [pgadmin-support] procedure properties problem
>
> If I omit the 'returns setof varchar' I will get only one
> result from select
> If I add 'returns setof varchar' I will get two results.

Ahh, yes, I see what you mean. Well, pgAdmin actually doesn't seem to
support set returning procedures at all. I've committed changes to trunk
and the stable branch to properly display the defintion of such objects,
and further changes to trunk to allow yoou to create them through the
GUI as well.

Thanks for the example.

Regards, Dave.