Thread: 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
>
> 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: > 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
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: > 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