Thread: 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