Thread: Stored Procedures

Is it possible to write a stored procedure which takes a struct as a
parameter?  I'd like to be able to pass in some info via a struct &
return some info via another struct something like this:

struct in_struct {
   integer i, j;
}

struct out_struct {
   integer k;
    char p;
}

create function myfunc(in_struct, out_struct) .....

Any comments?  From what I've read today, it doesn't look like I can do
this!

I'd appreciate any help.

Wendy (wendy@cs.queensu.ca)

Wendy,

> Is it possible to write a stored procedure which takes a struct as a
> parameter?  I'd like to be able to pass in some info via a struct &
> return some info via another struct something like this:

What's a "Struct"?  It's not a standard SQL term, and I don't recognize
it.


> struct in_struct {
>   integer i, j;
> }
>
> struct out_struct {
>   integer k;
>    char p;
> }

Are you trying for a custom data type, or more of a record?  Postgres
supports both.

> create function myfunc(in_struct, out_struct) .....

Postgres does not support output parameters, at present.  Functions
have one return value of a defined data type (which may be a custom
data type or a record, if you wish).

-Josh Berkus

Wendy,

> Here I'm referring to a "C struct" ... a data structure containing
> several values.  For instance, I might have a struct defined as
> followed:
>
>   struct person {
>      char name[10],
>       char address[30],
>        int  age };
>
> containing the name, address & age of a particular person.

Ah.  I see what you're attempting.

I'm not sure that you want to operate through functions at all.  There
is a very hefty C library for direct Postgres interaction.  You can
look it up in the online docs, or buy a book (such as Wrox Press's
Postgres book) which covers C + Postgresql programming.

That's as much as I know ... I'm a SQL jockey, and don't do C.

> What do you mean by a record here?  Sorry, I'm new to the postgres
> terminology (although quickly learning!!!).

A RECORD is a data type that holds field data for one to several
fields, and performs ... in PL/pgSQL functions ... a lot like the
Struct you mention.  However, a RECORD must be keyed off a query, as
follows:

DECLARE
    my_rec RECORD;
BEGIN
SELECT id, type INTO my_rec
FROM table1 WHERE id = $1;
IF my_rec.type = 'press contact' THEN

etc.

See the PL/pgSQL documentation (Under "Procedural Langauges" where
you'll find information on C functions as well) for more information
about this.

-Josh Berkus