On functions and stored procs
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
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
Show quoted text
Regards,
Graeme
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
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:
Show quoted text
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:
PQconnectdbNow, 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
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
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:
PQconnectdbNow, 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