Re: [GENERAL] help with "plpgsql"
am Mon, dem 21.04.2008, um 17:46:49 +0200 mailte Pau Marc Munoz Torres folgendes: > Hi everybody > > I trying to upload some plpsql functions to postgresql database using a perl > script and i get the following error > > > psql:/usr/local/Make2D-DB_II/pgsql/make2db_functions.pgsql:85: ERROR: > language "plpgsql" does not exist > HINT: Use CREATE LANGUAGE to load the language into the database. > > and then when I try to create the language, i get > > > geldb=# CREATE LANGUAGE plpgsql; > ERROR: language "plpgsql" already exists > > anybody knows what's wrong? Wild guess: you have different databases. The language exists in the database 'geldb', but your perl-script works on an other database. Andreas -- Andreas Kretschmer Kontakt: Heynitz: 035242/47150, D1: 0160/7141639 (mehr: -> Header) GnuPG-ID: 0x3FFF606C, privat 0x7F4584DA http://wwwkeys.de.pgp.net -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] help with "plpgsql"
Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II/pgsql/make2db_functions.pgsql:85: ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language "plpgsql" already exists anybody knows what's wrong? Thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] help with "plpgsql"
Pau Marc Munoz Torres wrote: Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language "plpgsql" already exists anybody knows what's wrong? Thanks My first guess would be what db are you connected to? Is the script connected to the same db you are connected to when you run psql? I would probably add the CREATE LANGUAGE command to the start of the perl script. -- Shane Ambler pgSQL (at) Sheeky (dot) Biz Get Sheeky @ http://Sheeky.Biz -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with "plpgsql"
On Apr 21, 2008, at 8:51 AM, Pau Marc Munoz Torres wrote: psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language "plpgsql" already exists Are you certain that you are dealing with the same database in both situations? -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with "plpgsql"
Pau Marc Munoz Torres wrote: ERROR: language "plpgsql" already exists anybody knows what's wrong? Is there any chance you might be connecting to a different database with the perl script and with psql? Procedural languages must be installed into a particular database. -- Craig Ringer -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] help with "plpgsql"
Hi everybody I trying to upload some plpsql functions to postgresql database using a perl script and i get the following error psql:/usr/local/Make2D-DB_II /pgsql/make2db_functions.pgsql:85: ERROR: language "plpgsql" does not exist HINT: Use CREATE LANGUAGE to load the language into the database. and then when I try to create the language, i get geldb=# CREATE LANGUAGE plpgsql; ERROR: language "plpgsql" already exists anybody knows what's wrong? Thanks -- Pau Marc Muñoz Torres Laboratori de Biologia Computacional Institut de Biotecnologia i Biomedicina Vicent Villar Universitat Autonoma de Barcelona E-08193 Bellaterra (Barcelona) telèfon: 93 5812807 Email : [EMAIL PROTECTED]
Re: [GENERAL] Help with plpgsql - subst variable value
*Alexander Bodnar * Try ObjectSeqName TEXT := foobar; EXECUTE 'CREATE SEQUENCE ' || ObjectSeqName || ' start 1 increment 1 maxvalue 2147483647'; Thanks, Chris Steffen ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [GENERAL] help with plpgsql function called by trigger
Thank you! That *is* nicer. And thanks to Michael Fuhr too for his reference to the appropriate docs and suggestions. Between your two responses, I have a much better sense of how to go about this and where to look for extra help. Heather Alban Hertroys wrote: Heather Johnson wrote: Hello-- I need to make sure that every time a row is inserted into a table called "users" rows are automatically inserted into two other tables: "join_bd" and "behavior_demographics". The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called "users_id"). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Wouldn't it be nicer to put the second part of that SP in a trigger on behavioral_demographics? That can only work if inserting into users is the only way to insert records into behavioral_demographics, of course. I don't know the names of your columns in behavioral_demographics, but it would look something like this: CREATE FUNCTION insert_bd () RETURNS trigger AS ' begin INSERT INTO behavioral_demographics (users_id) VALUES (NEW.users_id);-- fires trigger on bd end; ' LANGUAGE 'plpgsql'; CREATE FUNCTION insert_join_bd () RETURNS trigger AS ' begin INSERT INTO join_bd (bd_id, users_id) VALUES (NEW.bd_id, NEW.users_id); end; ' LANGUAGE 'plpgsql'; CREATE TRIGGER insert_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd(); CREATE TRIGGER insert_join_bd_on_users AFTER INSERT ON behavioral_demographics FOR EACH ROW EXECUTE PROCEDURE insert_join_bd(); Considering these SP's are basically simple SQL statements, the triggers could also be implemented as a set of query rewrite rules (see CREATE RULE). That's usually more efficient, but I don't have a lot of experience with those... Regards, ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] help with plpgsql function called by trigger
Heather Johnson wrote: Hello-- I need to make sure that every time a row is inserted into a table called "users" rows are automatically inserted into two other tables: "join_bd" and "behavior_demographics". The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called "users_id"). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Wouldn't it be nicer to put the second part of that SP in a trigger on behavioral_demographics? That can only work if inserting into users is the only way to insert records into behavioral_demographics, of course. I don't know the names of your columns in behavioral_demographics, but it would look something like this: CREATE FUNCTION insert_bd () RETURNS trigger AS ' begin INSERT INTO behavioral_demographics (users_id) VALUES (NEW.users_id); -- fires trigger on bd end; ' LANGUAGE 'plpgsql'; CREATE FUNCTION insert_join_bd () RETURNS trigger AS ' begin INSERT INTO join_bd (bd_id, users_id) VALUES (NEW.bd_id, NEW.users_id); end; ' LANGUAGE 'plpgsql'; CREATE TRIGGER insert_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd(); CREATE TRIGGER insert_join_bd_on_users AFTER INSERT ON behavioral_demographics FOR EACH ROW EXECUTE PROCEDURE insert_join_bd(); Considering these SP's are basically simple SQL statements, the triggers could also be implemented as a set of query rewrite rules (see CREATE RULE). That's usually more efficient, but I don't have a lot of experience with those... Regards, -- Alban Hertroys MAG Productions ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] help with plpgsql function called by trigger
On Wed, Mar 16, 2005 at 02:34:48PM -0500, Heather Johnson wrote:
> CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS '
What version of PostgreSQL are you using? The "opaque" type has
been deprecated since 7.3; recent versions should use "trigger".
And trigger functions aren't declared with arguments -- if the
function needs arguments, then use TG_ARGV. But I think your code
can use NEW instead of a function argument.
> declare
> r RECORD;
> uid ALIAS FOR $1;
> begin
> INSERT INTO behavioral_demographics (users_id) VALUES (uid);
> SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid;
> INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid);
> end;
> ' LANGUAGE 'plpgsql';
Based on the rest of your description, I think you're looking for
something like this:
CREATE FUNCTION insert_bd_join_bd() RETURNS trigger AS '
DECLARE
bdid integer;
BEGIN
INSERT INTO behavioral_demographics (users_id) VALUES (NEW.uid);
bdid := currval(''behavioral_demographics_bdid_seq'');
INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, NEW.uid);
RETURN NULL;
END;
' LANGUAGE plpgsql VOLATILE;
See the "Trigger Procedures" section of the PL/pgSQL chapter in the
documentation to learn more about NEW (and OLD, TG_ARGV, etc.), and
see the "Sequence Manipulation Functions" section of the "Functions
and Operators" chapter to learn more about currval().
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
[GENERAL] help with plpgsql function called by trigger
Hello-- I need to make sure that every time a row is inserted into a table called "users" rows are automatically inserted into two other tables: "join_bd" and "behavior_demographics". The inserts on join_bd and behavior_demographics need to create rows that are keyed to the users table with an integer id (called "users_id"). The join_bd row that's created also needs to contain a key for a record in behavior_demographics (bd_id). Here's what I did to try and accomplish this: CREATE FUNCTION insert_bd_join_bd (integer) RETURNS opaque AS ' declare r RECORD; uid ALIAS FOR $1; begin INSERT INTO behavioral_demographics (users_id) VALUES (uid); SELECT INTO r bdid FROM behavioral_demographics WHERE users_id=uid; INSERT INTO join_bd (bd_id, users_id) VALUES (bdid, uid); end; ' LANGUAGE 'plpgsql'; Now I want to trigger this function whenever there is an insert on the users table, so I did this: CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(); The problem is that I need to be able to send the value of the users_id that was generated by the insert into users to insert_bd_join_bd(). The users id value is generated by a sequence. So I think I'd need something like this: CREATE TRIGGER insert_bd_join_bd_on_users AFTER INSERT ON users FOR EACH ROW EXECUTE PROCEDURE insert_bd_join_bd(***USERS ID GENERATED BY SEQ FROM LAST USERS INSERT***); I'm new to writing plpgsql and to triggers, so I'm probably missing something, or doing this wrong, but I can't figure out from the docs how to send this value to the function. Can someone help point me to some docs that might help, or tell me why I'm on the wrong track? Thanks so much! Heather Johnson ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
[GENERAL] Help with plpgsql - subst variable value
Hi all. This my code snapshot: CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS ' DECLARE . . . ObjectSeqName text; BEGIN . . .. ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq''; CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE; . . . END IF; RETURN ObjectID; END; ' LANGUAGE 'plpgsql'; When I run this function I got ERROR: parser: parse error at or near "$1" So how can get value of ObjectSeqName in sequence/table/etc. name?
[GENERAL] Help with plpgsql - subst variable value
Hi all. This my code snapshot: CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS ' DECLARE . . . ObjectSeqName text; BEGIN . . .. ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq''; CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE; . . . END IF; RETURN ObjectID; END; ' LANGUAGE 'plpgsql'; When I run this function I got ERROR: parser: parse error at or near "$1" So how can get value of ObjectSeqName in sequence/table/etc. name?
[GENERAL] Help with plpgsql - subst variable value
Hi all. This my code snapshot: CREATE FUNCTION CreateObject(varchar(600)) RETURNS int4 AS ' DECLARE . . . ObjectSeqName text; BEGIN . . .. ObjectSeqName := ''ObjectLog'' || ObjectID || ''_Seq''; CREATE SEQUENCE ObjectSeqName start 1 increment 1 maxvalue 2147483647 minvalue 1 cache 1 CYCLE; . . . END IF; RETURN ObjectID; END; ' LANGUAGE 'plpgsql'; When I run this function I got ERROR: parser: parse error at or near "$1" So how can get value of ObjectSeqName in sequence/table/etc. name?
