VERSION
---------------------
PostgreSQL 7.3.6-RH
SCHEMA
--------
public
PROBLEM
--------
Is the word 'var_usr_id' reserved ANYWHERE? I can't seem to use it as a variable in plpgsql. I get this error when I
callthe function from the command line (using phpPgAdmin - haven't checked anywhere else ):
ERROR
-------
SQL error:
ERROR: syntax error at or near "var_usr_id"
RESOLUTION
----------
If I change it to 'dvar_usr_id', it works.
RESEARCHED:
-----------
I searched the entire 7.4 manual and did not find that phrase - 'var_usr_id'
ANYWHERE.
EXTRA
--------
I have tables defined which have the column name 'usr_id'. None of the tables
are defined as 'var'. Their definitions are at the bottom in case needed.
************************FUNCTION CALL*************************
--------------------------------------------------------------
SELECT * FROM events.create_usr(
'dude'::text,
'4534532ABCDEF45T4523ABCDEF'::text,
'george@carlin.com'::text,
'home'::text,
'ABCDEF4352345235ABCDEF5421432ABCDEF'::text,
'Gearon'::text,
'Dennis'::text,
'K'::text,
'jr'::text) AS ( status int, message text );
**********************FUNCTION DEFINITION*********************
--------------------------------------------------------------
-- see func_example.sql for how to create and use a function
--
-- return type should be RECORD
-- with status INT,
-- and message TEXT.
--
SET search_path TO events;
CREATE OR REPLACE FUNCTION create_usr(
text,
text,
text,
text,
text,
text,
text,
text,
text)
RETURNS RECORD AS '
DECLARE
--configuration
--these must match the indexes, triggers, and contraints on the tables:
-- Usr
-- UsrEmails
-- UsrEmailTypes
--
--see the document UsrEmailCfgChart.html
cfg_many_emails_per_usr bool := TRUE;
cfg_many_usrs_per_email bool := FALSE;
cfg_many_emails_per_type_per_usr bool := FALSE;
cfg_many_types_avail bool := TRUE;
cfg_many_types_can_be_pri bool := FALSE;
var_login text;
var_hashed_pw text;
var_pri_email text;
var_pri_email_type text;
var_email_verify_hash text;
var_sur_name text;
var_first_name text;
var_mid_name text;
var_gen text;
var_usr_id integer;
var_email_type_rec record;
var_email_rec record;
var_gen_rec record;
var_failed bool;
var_pri_email_id integer;
var_usr_id integer;
var_record_out record;
var_message_out text;
var_status_out integer;
BEGIN
var_failed := TRUE;
var_login := $1;
var_hashed_pw := $2;
var_pri_email := $3;
var_pri_email_type := $4;
var_email_verify_hash := $5;
var_sur_name := $6;
var_first_name := $7;
var_mid_name := $8;
var_gen := $9;
var_login := trim( both FROM $1 );
var_hashed_pw := trim( both FROM $2 );
var_pri_email := trim( both FROM $3 );
var_pri_email_type := trim( both FROM $4 );
var_email_verify_hash := trim( both FROM $5 );
var_sur_name := trim( both FROM $6 );
var_first_name := trim( both FROM $7 );
var_mid_name := trim( both FROM $8 );
var_gen := trim( both FROM $9 );
var_status_out := -1;
var_message_out := ''original'';
SELECT INTO var_record_out
var_status_out AS status,
var_message_out AS message;
RETURN var_record_out;
END;
' LANGUAGE 'plpgsql';
**************TABLE DEFINITIONS*****************
------------------------------------------------
CREATE TABLE Usrs (
usr_id SERIAL NOT NULL,
login TEXT NOT NULL UNIQUE,
hashed_pwd TEXT NOT NULL,
sur_name TEXT NOT NULL,
first_name TEXT NOT NULL,
mid_name TEXT DEFAULT '' NOT NULL,
gen_id INT4 NOT NULL,
enabled BOOL DEFAULT TRUE NOT NULL,
CONSTRAINT PK_Usrs PRIMARY KEY (usr_id)
);
CREATE TABLE EmailAddrs (
email_addr_id SERIAL NOT NULL,
email_addr TEXT NOT NULL UNIQUE,
CONSTRAINT PK_EmailAddrs PRIMARY KEY (email_addr_id)
);
CREATE TABLE UsrEmails (
usr_id INT4 NOT NULL,
email_addr_id INT4 NOT NULL,
usr_email_type_id INT4 NOT NULL,
email_verify_hash TEXT NOT NULL,
validation_timestamp_tz TIMESTAMP WITH TIME ZONE DEFAULT 'infinity',
CONSTRAINT PK_UsrEmails PRIMARY KEY (usr_id, email_addr_id, usr_email_type_id)
);
CREATE TABLE UsrEmailTypes (
usr_email_type_id SERIAL NOT NULL,
usr_email_type TEXT NOT NULL UNIQUE,
pri BOOL NOT NULL,
multiple_per_usr BOOL NOT NULL,
CONSTRAINT PK_UsrEmailTypes PRIMARY KEY (usr_email_type_id)
);
CREATE TABLE Gens (
gen_id SERIAL NOT NULL,
gen VARCHAR(16) DEFAULT 'none' NOT NULL UNIQUE,
CONSTRAINT PK_Gens PRIMARY KEY (gen_id)
);
ALTER TABLE Usrs
ADD CONSTRAINT Gens11_0MUsrs FOREIGN KEY (gen_id) REFERENCES Gens (gen_id);
ALTER TABLE UsrEmails
ADD CONSTRAINT EmailAddrs11_0MUsrEmail FOREIGN KEY (email_addr_id) REFERENCES EmailAddrs (email_addr_id);
ALTER TABLE UsrEmails
ADD CONSTRAINT UsrEmailTypes11_0MUsrEmails FOREIGN KEY (usr_email_type_id) REFERENCES UsrEmailTypes
(usr_email_type_id);
ALTER TABLE UsrEmails
ADD CONSTRAINT Usrs11_1MUsrEmails FOREIGN KEY (usr_id) REFERENCES Usrs (usr_id);
CREATE UNIQUE INDEX IDXU_EmailAddrs ON EmailAddrs (email_addr);
CREATE UNIQUE INDEX IDXU_UsrEmailTypes ON UsrEmailTypes (usr_email_type);
CREATE UNIQUE INDEX IDXU_Gens ON Gens (gen);
INSERT INTO UsrEmailTypes ( usr_email_type, pri, multiple_per_usr ) VALUES ( 'home'::TEXT, TRUE, FALSE );
INSERT INTO UsrEmailTypes ( usr_email_type, pri, multiple_per_usr ) VALUES ( 'work'::TEXT, TRUE, FALSE );
INSERT INTO Gens ( gen ) VALUES ( 'jr'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'sr'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'i'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'ii'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'iii'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'iv'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'v'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'vi'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'vii'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'viii'::TEXT );
INSERT INTO Gens ( gen ) VALUES ( 'x'::TEXT );
