PostgreSQL failed on Select

postgresql

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;

Best Answer

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;
Related Question