PostgreSQL failed on Select

Question

I am trying to use a result from a select for another select:

DECLARE 
_port int;
BEGIN
select alter_port into _port from database where ....;
select data inot data_list from database where PortNumber = alter_port;

The second select always fails, the first select returns the data I want.

But, when I force assign value

alter_port := 2602;
select data from database where PortNumber = alter_port;

Then I get the data I need.

Why has this failed? I know the table has the data I want. How do I check Error code for select statement?

Below are the table and function definitions:

CREATE TABLE AltRoute (
 GUID uuid PRIMARY KEY,
 PortNumber int NULL,
 ControllerNumber int NULL ,
 AltRtePortNumber int NULL ,
 AltRtePortName varchar (256) NULL ,
 AltRtePortList varchar(300) NOT NULL DEFAULT '0',
 HighAltPort int NULL DEFAULT 0,
 MediumAltPort int NULL DEFAULT 0,
 LowAltPort int NULL DEFAULT 0,
 HighEnable int NULL CONSTRAINT DF_AltRoute_HighEnable DEFAULT (0),
 MediumEnable int NULL CONSTRAINT DF_AltRoute_MediumEnable DEFAULT (0),
 LowEnable int NULL CONSTRAINT DF_AltRoute_LowEnable DEFAULT (0),
 HighPortList varchar (405) NULL CONSTRAINT DF_AltRoute_HighPortList DEFAULT (0),
 MediumPortList varchar (405) NULL CONSTRAINT DF_AltRoute_MediumPortList DEFAULT (0),
 LowPortList varchar (405) NULL CONSTRAINT DF_AltRoute_LowPortList DEFAULT (0), 
 DynamicQuery varchar (4000) NOT NULL DEFAULT 0
);
CREATE FUNCTION sp_ComposeHighPortList (_startPort int) RETURNS integer AS $$
DECLARE
_altRtePort INT = 0;
_altRtePortList VARCHAR (405) = '0';
_port INT = 0;
BEGIN 
 _altRtePort := 0;
 _altRtePortList := '1';
 _port := 0;
 SELECT HighAltPort into _altRtePort
 FROM AltRoute
 WHERE PortNumber = _startPort and HighEnable = 1;
 IF (NOT FOUND) THEN
 RAISE NOTICE 'sp_ComposeHighPortList - FAILED: SELECT _altRtePort = HighAltPort FROM AltRoute WHERE PortNumber = _startPort and HighEnable = 1, _startPort=%', _startPort;
 RETURN 0;
 END IF;
 IF (_altRtePort = 0 or _altRtePort = _startPort) THEN
 _altRtePortList := '';
 _altRtePort := 0;
 _startPort := _startPort+1;
 RETURN 0;
 END IF;
 SELECT RTRIM(HighPortList) into _altRtePortList
 FROM AltRoute
 WHERE PortNumber = _altRtePort;
 IF (NOT FOUND) THEN 
 RAISE NOTICE 'sp_ComposeHighPortList - FAILED: SELECT _altRtePortList = RTRIM(HighPortList) FROM AltRoute WHERE PortNumber = _altRtePort : %', _altRtePort;
 END IF;
...
...
..
END;
$$ LANGUAGE plpgSQL;

• Kassandry: Are you using a plpgsql function for this? From your code it looks like you are but it isn't clear in your question. Could you please clarify?
• Erwin Brandstetter: inot instead of into is just a typo in the question or part of the problem? Also, data_list is not declared in your example. Please provide a complete function, table definitions and always your version of Postgres.
• user1822: No need for a variable (at least not in this case) select data into data_list from some_table where PortNumber = (select alter_port from another_table where ...). Note that this will fail if the sub-select returns more than one row, but so would the select ... into

Answer 1

I think it should be;

 DECLARE 
 _port int;
 BEGIN
 select alter_port into _port from a_table where ....;
 select data into data_list from a_table where PortNumber = _port;

• A Chang: The problem is use _port from first select failed the second select. I write _port from first select to a debugging table. The data is correct. Yet, in second select it falls to the IF (NOT FOUND) condition.
• Erwin Brandstetter: @AChang: Are you going to update your question to provide missing information?
• A Chang: @Erwin, I don't know how to add long commend. So I use answer my own question.