Skip site navigation (1) Skip section navigation (2)

wierd reserved word?

From: gearond(at)fireserve(dot)net
To: <pgsql-general(at)postgresql(dot)org>
Subject: wierd reserved word?
Date: 2004-09-26 21:39:05
Message-ID: 200409262139.i8QLd5s0003873@phaze.fireserve.net (view raw or flat)
Thread:
Lists: pgsql-general
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 call the 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(at)carlin(dot)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 );








pgsql-general by date

Next:From: gearondDate: 2004-09-26 21:47:57
Subject: Re: wierd reserved word?
Previous:From: Joshua D. DrakeDate: 2004-09-26 19:10:51
Subject: Re: checksum

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group