0

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;
  • 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? – Kassandry Aug 28 '15 at 5:19
  • 1
    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. – Erwin Brandstetter Aug 28 '15 at 6:53
  • 1
    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 – a_horse_with_no_name Aug 28 '15 at 11:28
0

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;
  • 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. – A Chang Aug 28 '15 at 17:06
  • @AChang: Are you going to update your question to provide missing information? – Erwin Brandstetter Aug 28 '15 at 21:52
  • @Erwin, I don't know how to add long commend. So I use answer my own question. – A Chang Aug 31 '15 at 13:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.