Thread: how to do plpgsql?
When trying to do some of the examples on http://www.postgresql.org/docs/postgres/c40914344.htm I keep getting: ERROR: Unrecognized language specified in a CREATE FUNCTION: 'plpgsql'. Recognized languages are sql, C, internal and the created procedural languages. version is:PostgreSQL 7.0.3 on i686-pc-linux-gnu, compiled by gcc egcs-2.91.66 -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
Joseph,
First you need to install plpgsql on a per database
basis, or you can just install it on template1 and it
will get added to all new databases.
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
-Josh Berkus
(Instructions courtesy of Jeff at PGSQL Inc.)
--
______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete
informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small
businesses fax 621-2533 and non-profit organizations. San Francisco
Huh. You'd think this would be prominent in the documentation page at http://www.postgresql.org/docs/postgres/c4091.htm Thanks. Josh Berkus wrote: > > Joseph, > > First you need to install plpgsql on a per database > basis, or you can just install it on template1 and it > will get added to all new databases. > > CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS > '/usr/lib/pgsql/plpgsql.so' LANGUAGE 'C'; > > CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER > "plpgsql_call_handler" LANCOMPILER 'PL/pgSQL'; > > -Josh Berkus > > (Instructions courtesy of Jeff at PGSQL Inc.) > -- > ______AGLIO DATABASE SOLUTIONS___________________________ > Josh Berkus > Complete information technology josh@agliodbs.com > and data management solutions (415) 565-7293 > for law firms, small businesses fax 621-2533 > and non-profit organizations. San Francisco -- Joseph Shraibman jks@selectacast.net Increase signal to noise ratio. http://www.targabot.com
On Wed, 07 Feb 2001 14:18, Joseph Shraibman wrote: > Huh. You'd think this would be prominent in the documentation page at > http://www.postgresql.org/docs/postgres/c4091.htm Thanks from me for that one too. What about incorporating that particular functionality into the initdb program. Strikes me that this should be available by default as part of the installed product. On the other hand I might be missing something, if so I'd like to be informed. Thanks. -- Sincerely etc., NAME Christopher SawtellCELL PHONE 021 257 4451ICQ UIN 45863470EMAIL csawtell @ xtra . co . nzCNOTES ftp://ftp.funet.fi/pub/languages/C/tutorials/sawtell_C.tar.gz ->> Please refrain from using HTML or WORD attachments in e-mails to me <<-
Tom et al. Discovered this quirk in foriegn keys: In the preliminary version of a database, I added foriegn key constraints to a number of tables, linking them to a column in a shared reference table (status.status) that was only one-half of a composite primary key (and thus the values were not unique). When I tried to delete a row containing a "2" in the status column from the status relation, I received a Foreign Key violation error event though there were other "2"'s in the table still present. So ... is this a bug in forign key implementation, or just my fault for keying off a non-unique value? And, if the latter, is there a way I can construct a foreign key constraint that keys onto a view or query? Grazie! -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
Technically you are not allowed to make an FK to non-unique values. What you're closer to looking for is MATCH PARTIAL which we don't support (because it's a real pain - although with the new memory management stuff in 7.1 it may be less of one - since the fundamental problem is storing values from other iterations of the trigger for this last update/delete for ref actions). 7.1 won't let you define such a constraint with the create table or alter table syntaxes (I guess theoretically it would let you create constraint trigger and bring the broken constraint from an older version). Right now we don't support constraining views because we don't have a mechanism in place to rewrite the constraint to actually work. On Tue, 6 Feb 2001, Josh Berkus wrote: > Tom et al. > > Discovered this quirk in foriegn keys: > > In the preliminary version of a database, I added foriegn > key constraints to a number of tables, linking them to a > column in a shared reference table (status.status) that was > only one-half of a composite primary key (and thus the > values were not unique). When I tried to delete a row > containing a "2" in the status column from the status > relation, I received a Foreign Key violation error event > though there were other "2"'s in the table still present. > > So ... is this a bug in forign key implementation, or just > my fault for keying off a non-unique value? > > And, if the latter, is there a way I can construct a foreign > key constraint that keys onto a view or query?
Josh Berkus wrote: > Tom et al. > > Discovered this quirk in foriegn keys: > > In the preliminary version of a database, I added foriegn > key constraints to a number of tables, linking them to a > column in a shared reference table (status.status) that was > only one-half of a composite primary key (and thus the > values were not unique). When I tried to delete a row > containing a "2" in the status column from the status > relation, I received a Foreign Key violation error event > though there were other "2"'s in the table still present. > > So ... is this a bug in forign key implementation, or just > my fault for keying off a non-unique value? > > And, if the latter, is there a way I can construct a foreign > key constraint that keys onto a view or query? The referenced columns of a foreign key constraint shall have a unique constraint. That's how it is in the SQL specs. So it is a bug that the system let's you specify the constraint at all. I think Stephan fixed it for 7.1. OTOH Postgres doesn't (and shouldn't) enforce it after, because if it would, you couldn't drop/create a corrupted index. And no, you can't actually reference to a view or anything else than a table. That is, because the system wouldn'tknow how to check for the DELETE/UPDATE cases on the base tables building the view if the removal ofa key would violate existing references. For such custom setups, we have procedural languages and triggers. Jan -- #======================================================================# # It's easier to get forgiveness for being wrong than for being right. # # Let's break this rule - forgive me. # #================================================== JanWieck@Yahoo.com # _________________________________________________________ Do You Yahoo!? Get your free @yahoo.com address at http://mail.yahoo.com