SELECT INTO returns incorrect values
The following SELECT INTO returns incorrect values in the variables
CATEGORY_NAME and PARENT_ID. If I copy the SELECT statement to pgAdmin,
delete the INTO clause and run the query it returns the correct values.
I am new to PostgreSQL and I must have something syntactically wrong in
the SELECT but I can't see what it is. Any suggestions?
DECLARE
PARENT_ID BIGINT;
CATEGORY_NAME VARCHAR(40);
BEGIN
SELECT CATEGORY, PARENT_CATEGORY_ID
INTO CATEGORY_NAME, PARENT_ID
FROM NOTE.CATEGORY
WHERE CATEGORY_ID = 477;
RAISE NOTICE 'CURR CAT, NAME, PARENT ID: % % ', CATEGORY_NAME,
PARENT_ID;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
Thanks,
Bill
Bill Todd <pg@dbginc.com> writes:
The following SELECT INTO returns incorrect values in the variables
CATEGORY_NAME and PARENT_ID. If I copy the SELECT statement to pgAdmin,
delete the INTO clause and run the query it returns the correct values.
You didn't show us the whole function definition, so this is just
speculation, but I wonder whether any of the function's variable or
parameter names match any of the table column names used in the query.
For instance if you had a function parameter named CATEGORY or
CATEGORY_ID, you'd get surprising results because the value of that
parameter would get substituted for what you're thinking is a column
reference.
regards, tom lane
Bill,
Did you try it like this:
parent_id = 0
category_name = ''
select category, parent_category_id
from note.category
where category_id = 477 into category_name, parent_id;
raise notice 'curr cat, name, parent id: % % ', category_name,
parent_id;
I have found in the past that it's a good idea to initialize your vars
before you use them in PL/pgsql.
Also as a FYI, you don't need to upper case all your text in a function
(I know you have to do that in Firebird), just use standard case with
normal capitalization because
PostgreSQL will lowercase everything you send to the server that is not
in quotes. It's a lot easier to read without the uppercase.
Later,
Tony Caduto
AM Software Design
Home of Lightning Admin for PostgreSQL
http://www.amsoftwaredesign.com