Thread: On functions and stored procs

On functions and stored procs

From
"Graeme Merrall"
Date:
I'm just getting into functions in postgres and I've bumped up against a
couple issues which I think I need explained. I've had a wee read of the
archives on this but haven't turned up to much. I think it may be a
conceptual problem on my part though :)

Is it possible on postgres, using pl/pgsql to create a function that is
essentially a stored procedure? i.e. go through and execute a series of SQL
statements and return a value - success or failure for example. The examples
in the docs revolve more about creating functions from, well, a function
point of view rather than a stored procedure type of view.
In that I should probably say my only exposure with SP's in from MS-SQL so I
mean that definition. :)

Aditionally, from reading the docs on pl/pgsql is it possible to loop
through a set of rows returned from a query and perform an action on each
iteration?
As an example, I query a table for a set of user accounts that need
processing on a certain day (today). Is it possible to query the table,
return a set of results then loop through those results and on each pass,
insert data into another table(s)?
The conditional I'm referring to in the docs is
[<<label>>]
FOR record | row IN select_clause LOOP
    statements
END LOOP;

Any examples of this sort of thing? Quiet obviously, the docs generally need
updating when it comes to functions. Is there a documentation project/team?

Regards,
 Graeme



Re: On functions and stored procs

From
Ed Loehr
Date:
Graeme Merrall wrote:
>
> I'm just getting into functions in postgres and I've bumped up against a
> couple issues which I think I need explained. I've had a wee read of the
> archives on this but haven't turned up to much. I think it may be a
> conceptual problem on my part though :)
>
> Is it possible on postgres, using pl/pgsql to create a function that is
> essentially a stored procedure? i.e. go through and execute a series of SQL
> statements and return a value - success or failure for example.

Yes, this is possible with plpgsql.  Note you're limited to returning one
value, not a relation.

> Aditionally, from reading the docs on pl/pgsql is it possible to loop
> through a set of rows returned from a query and perform an action on each
> iteration?

Yes.

> Any examples of this sort of thing?

    .../src/test/regress/sql/plpgsql.sql

> Quiet obviously, the docs generally need
> updating when it comes to functions. Is there a documentation project/team?

    www.postgresql.org
>
> Regards,
>  Graeme

well, shoot. Error loading Pg.so

From
"Steve Wolfe"
Date:
   Today, our newest employee thought he'd upgrade the Perl interface to
Postgres.  So, he went into the source directory, and did "gmake install"
in the /src/interfaces/Perl5 directory.  Now, everything's broken.  Trying
to use it, we get:

perl: error in loading shared libraries:
/usr/lib/perl5/site_perl/i386-linux/auto/Pg/Pg.so: undefined symbol:
PQconnectdb

  Now, here's where it gets reeeeely nice.  The last system admin, who is
now gone, has about 50 copies of the postgress source lying around on the
disk (really bright guy).  They're all 6.5.3, except for the rpm's for
6.5.3-2 and 6.5.3-2nl.

  So, at this point, I have two choices:  Dump all data, reinstall, and
restore (a few days of dumping/restoring), or some nice soul can suggest
how I can fix this....

  Any suggestions?

steve


Re: well, shoot. Error loading Pg.so

From
Charles Tassell
Date:
Well, a couple of suggestions:

Restore the /usr/lib/perl5/site_perl directory from a working backup.

Try doing a ./configure --with-perl in the src directory, then go into
interfaces/Perl5 and type make;make install.  Maybe the currently installed
src dir wasn't correct.

Might also want to check that /usr/local/pgsql/lib is in your
/etc/ld.so.conf file and re-run ldconfig.  (Probably should do this before
trying the rebuild of the perl interface)


At 03:04 PM 4/19/00, Steve Wolfe wrote:

>    Today, our newest employee thought he'd upgrade the Perl interface to
>Postgres.  So, he went into the source directory, and did "gmake install"
>in the /src/interfaces/Perl5 directory.  Now, everything's broken.  Trying
>to use it, we get:
>
>perl: error in loading shared libraries:
>/usr/lib/perl5/site_perl/i386-linux/auto/Pg/Pg.so: undefined symbol:
>PQconnectdb
>
>   Now, here's where it gets reeeeely nice.  The last system admin, who is
>now gone, has about 50 copies of the postgress source lying around on the
>disk (really bright guy).  They're all 6.5.3, except for the rpm's for
>6.5.3-2 and 6.5.3-2nl.
>
>   So, at this point, I have two choices:  Dump all data, reinstall, and
>restore (a few days of dumping/restoring), or some nice soul can suggest
>how I can fix this....
>
>   Any suggestions?
>
>steve


Re: well, shoot. Error loading Pg.so

From
"Steve Wolfe"
Date:
  First, I really appreciate the suggestions.  On to a reply...

> Restore the /usr/lib/perl5/site_perl directory from a working backup.

  You'd think so, wouldn't you?  : )

  Unfortunately, the last time we put a cleaning tape in the DAT drive, the
drive died, and now needs to be replaced.  Because we're so vastly
over-worked, that won't happen for a while.  To make it worse, since we're
so vastly over-loaded, we never had the time to make the DAT drive work in
the first place.

> Try doing a ./configure --with-perl in the src directory, then go into
> interfaces/Perl5 and type make;make install.  Maybe the currently
installed
> src dir wasn't correct.

  The bad thing is that the last admin (who is now gone) installed 6.3 and
deleted the source - and I can't find the 6.3 source from postgresql.org.

> Might also want to check that /usr/local/pgsql/lib is in your
> /etc/ld.so.conf file and re-run ldconfig.  (Probably should do this
before
> trying the rebuild of the perl interface)

  I'll give that a shot, thanks a million.

steve


Re: well, shoot. Error loading Pg.so

From
Bruce Momjian
Date:
I recommend building the entire thing, then grabbing the perl part.

[Charset iso-8859-1 unsupported, filtering to ASCII...]
>
>    Today, our newest employee thought he'd upgrade the Perl interface to
> Postgres.  So, he went into the source directory, and did "gmake install"
> in the /src/interfaces/Perl5 directory.  Now, everything's broken.  Trying
> to use it, we get:
>
> perl: error in loading shared libraries:
> /usr/lib/perl5/site_perl/i386-linux/auto/Pg/Pg.so: undefined symbol:
> PQconnectdb
>
>   Now, here's where it gets reeeeely nice.  The last system admin, who is
> now gone, has about 50 copies of the postgress source lying around on the
> disk (really bright guy).  They're all 6.5.3, except for the rpm's for
> 6.5.3-2 and 6.5.3-2nl.
>
>   So, at this point, I have two choices:  Dump all data, reinstall, and
> restore (a few days of dumping/restoring), or some nice soul can suggest
> how I can fix this....
>
>   Any suggestions?
>
> steve
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026