Обсуждение: Newbie: help with FUNCTION

I'm trying to create  a function that takes 1
paramater (eg an integer) as input, then does 5
database updates or deletes in 5 different SQL
statements and returns 5 integers (in one resultset)
indicating how many rows were affected by the various
updates/deletes.

How do I do this?

How can I specify the names of the 5 output colums?

Thanks

Charl Gerber wrote:
> I'm trying to create  a function that takes 1
> paramater (eg an integer) as input, then does 5
> database updates or deletes in 5 different SQL
> statements and returns 5 integers (in one resultset)
> indicating how many rows were affected by the various
> updates/deletes.
>
> How do I do this?
>
> How can I specify the names of the 5 output colums?

Why not return 5 rows instead. That way you can extend it to 6 queries
easily.

CREATE TYPE num_rows_affected AS (
   tbl_name text,
   num_rows int4
);

CREATE FUNCTION do_stuff(int4) RETURNS SETOF num_rows_affected AS '
DECLARE
   res      num_rows_affected;
BEGIN
   -- Do query 1 here
   GET DIAGNOSTICS res.num_rows := ROW_COUNT;
   res.tbl_name := ''table1'';
   RETURN NEXT res;
   -- Do query 2 here
   GET DIAGNOSTICS res.num_rows := ROW_COUNT;
   res.tbl_name := ''table1'';
   RETURN NEXT res;
...etc...
   RETURN;
END;
' LANGUAGE plpgsql;


SELECT * FROM do_stuff(123);

Full details in the plpgsql chapter of the manuals. None of the above is
tested for syntax errors. You can use block-quoting in version 8.0

--
   Richard Huxton
   Archonet Ltd

Alternatives to returning a set of rows is to return
a set of integers, one per row selected.  Same example
as Huxton's without the row type.

Also you could return an array of integers.

--elein

On Thu, Feb 24, 2005 at 09:00:46PM +0000, Charl Gerber wrote:
> I'm trying to create  a function that takes 1
> paramater (eg an integer) as input, then does 5
> database updates or deletes in 5 different SQL
> statements and returns 5 integers (in one resultset)
> indicating how many rows were affected by the various
> updates/deletes.
>
> How do I do this?
>
> How can I specify the names of the 5 output colums?
>
> Thanks
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

> > On Thu, Feb 24, 2005 at 09:00:46PM +0000, Charl Gerber wrote:
> > I'm trying to create  a function that takes 1
> > paramater (eg an integer) as input, then does 5
> > database updates or deletes in 5 different SQL
> > statements and returns 5 integers (in one resultset)
> > indicating how many rows were affected by the various
> > updates/deletes.
> >
> > How do I do this?

You could return an array like elein recommended....

> > How can I specify the names of the 5 output colums?

but you won't have "names" associated with the output columns. To have
something other than integers as the index (such as a string), you
need to use an associative array.

sub foo
{
    my $integer = shift;

    my $ret1 = $dbh->do('some sql here');

    my $ret2 = $dbh->do('some sql here');

    # etc.... for 5 statements.

    my %hash = (
        'Column Label 1' => $ret1,
        'Column Label 2' => $ret2,
        #..... etc...
    );

     return \%hash;
}

So, you should read about associative arrays (aka hashes) and references.

Rick