Thread: composite types

I'd like to compute some "row like" results and return them from a
function (and pass the result to PHP[*]).

Well I've read about composite type but all the references I've seen
are about coding in C. That's not the way I'd like to follow by now.

I was expecting to declare composite types in plpsql or plain sql in a
similar way I'd do in C/C++ with struct/class.

I've found this
http://archives.postgresql.org/pgsql-general/2001-03/msg01459.php
but I'd like to avoid to declare a table just for a temp.

pseudocode follow

declare struct pippo(
    integer a;
    varchar(8) b;
)

create or replace function functiontest( ) returns pippo
as '
declare
    integer tempa;
    varchar(8) tempb;

begin
    /*
    do stuff to fill tempa and tempb
    */
    return (tempa,tempb)
end
' language plpgsql;

[*] I guess that to access the result of such a function
...
$SQLResult = pg_exec( $SQLConn, "SELECT functiontest( );" );
$SQLData = pg_fetch_row( $SQLResult, 0 );
should be enough...

thx

On Tue, 20 Apr 2004 17:41:30 +0200
Ivan Sergio Borgonovo <mail@webthatworks.it> wrote:

> I'd like to compute some "row like" results and return them from a
> function (and pass the result to PHP[*]).
>
> Well I've read about composite type but all the references I've seen
> are about coding in C. That's not the way I'd like to follow by now.
>
> I was expecting to declare composite types in plpsql or plain sql in
> a similar way I'd do in C/C++ with struct/class.

<shame>
http://pgsqld.active-venture.com/sql-createtype.html
This seems more like what I was looking for.
</shame>

I'll do some tests tomorrow and get back to the list if it isn't what
I was looking for.

Ivan Sergio Borgonovo wrote:

> I've found this
> http://archives.postgresql.org/pgsql-general/2001-03/msg01459.php
> but I'd like to avoid to declare a table just for a temp.
>
> pseudocode follow
>
> declare struct pippo(
>     integer a;
>     varchar(8) b;
> )
>
> create or replace function functiontest( ) returns pippo
> as '
> declare
>     integer tempa;
>     varchar(8) tempb;
>

I think you might want to look at SRFs (Set Returning Functions).  Here
are a couple of links:

http://techdocs.postgresql.org/guides/SetReturningFunctions
http://www.varlena.com/varlena/GeneralBits/26.html