From: | Ron St-Pierre <rstpierre(at)syscor(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | Function - sequence - cast |
Date: | 2004-05-18 22:10:36 |
Message-ID: | 40AA89DC.2080302@syscor.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I am trying to use a sequence value in a function but I keep getting an error message:
WARNING: Error occurred while executing PL/pgSQL function correctaddress
WARNING: line 8 at SQL statement
ERROR: column "addressid" is of type integer but expression is of type character varying
You will need to rewrite or cast the expression
And the function looks like:
CREATE FUNCTION correctAddress(INT) RETURNS INT AS '
DECLARE
user_id ALIAS FOR $1;
old_addr INT;
new_addr INT;
BEGIN
PERFORM nextval(''public.address_addressid_seq'');
INSERT INTO address (SELECT strProvince, strAddress FROM address WHERE addressID = (SELECT addressID FROM companies WHERE companyID = (SELECT companyID FROM users WHERE userID=user_id)));
UPDATE users SET adressID = CAST(currval(''public.tbladdress_addressid_seq'') AS INTEGER) WHERE userID=user_id;
-- ---> ^ ^ ^ ^ ^ ^ = ?
RETURN 1;
END ' LANGUAGE 'plpgsql';
It's probably something simple that I'm doing wrong. Can anyone help?
Postgres 7.4.1
Thanks
Ron
From | Date | Subject | |
---|---|---|---|
Next Message | Brian Hirt | 2004-05-19 01:37:11 | pg_autovacuum seems to be a neat freak and cleans way too much |
Previous Message | Taras V. Panchenko | 2004-05-18 20:30:43 | initdb fails |