Postgresql stored procedure temporary table variable is not a known variable-CodePudding

I've been doing research and trying things out a bunch of different ways, but with no success. I want to create a temporary table and then as I'm doing some searches, fill it up with stuff. I was able to do this with SQL Server by just declaring a table inside the procedure, but with Postgresql I've read I need to create a temporary table specifically.

My strategy started out with just

CREATE TEMP TABLE myTempTable
( 
    propOne bigint, 
    propTwo smallint, 
    createdAtUtc timestamp(6)
);

I even moved it to right after the "BEGIN". Down the file I get this error:

ERROR: "myTempTable" is not a known variable
LINE 77: SELECT * INTO myTempTable from myResult;

Next, I tried to create the temp table when I'm ready to fill it...

WITH some_updated_records AS 
(
    UPDATE dbTable
    SET tablePropertyStatus = 3
    WHERE tablePropertyDate < storedProcedurePropertyDate
    RETURNING *
)
CREATE TEMP TABLE myTempTable as 
(
    SELECT * 
    FROM some_updated_records
);

I still get the same basic error above, but zero errors until it encounters the myTempTable variable.

I'm definitely not a SQL genius (perhaps, eventually, with your help), so there might be some other things I'm doing wrong. My whole task is to convert a SQL Server stored procedure to Postgresql.

What could I being doing wrong to make that temporary table variable un-declared? Is there a special way I need to declare it ahead of time? Am I making a mistake about how to create or declare a temporary table.

Another strategy could be to just keep saving records into a collection of types, forget the "temp table." Is there a way to do this in plpgsql?

UPDATE w/Examples

This version doesn't work. It stops at the create table.

CREATE OR REPLACE PROCEDURE MyTestProcedure(
    p_Endpoint Varchar(256),
    p_ContentType Varchar(200),
    MaxInProcess int = NULL)

LANGUAGE plpgsql

AS $body$

DECLARE
v_UtcNow timestamp(6);
v_ExpiredProcessing timestamp(6);

BEGIN
    SELECT CURRENT_TIMESTAMP into v_UtcNow at time zone 'utc';

    WITH first_updated AS (UPDATE MyTable
        SET Status = 1
        WHERE UpdatedAtUtc < v_UtcNow
        RETURNING Id, Status, UpdatedAtUtc)
        
    CREATE TEMP TABLE IF NOT EXISTS statustable AS (SELECT Id, Status, UpdatedAtUtc FROM first_updated)

    WITH m_result AS (UPDATE MyTable
    SET Status = 3,
    WHERE ExpirationDateTimeUtc < v_UtcNow
    RETURNING Id, Status, UpdatedAtUtc)
    
    INSERT INTO statustable from m_result;
            
    DROP TABLE statustable;
    
END;
$body$

This errors out at the table creation.

INE 22:  CREATE TEMP TABLE statustable as...

The other example would be something similar to creating the table first and then inserting into it. That's probably where I messed up. Working solution will be added in a minute, if someone doesn't add it in first.

CodePudding user response：

You can use a CTE, but put the CTE within the parentheses for the table creation.

CREATE TEMPORARY TABLE myTempTable AS (
  WITH cte_updated_records AS (
    UPDATE dbTable
    SET tablePropertyStatus = 3
    WHERE tablePropertyDate < storedProcedurePropertyDate
    RETURNING *
  )
  SELECT * FROM cte_updated_records
);

CodePudding user response：

When you're debugging, things can get a little crazy. What happens often, I find, is I try one good solution, but I don't know how to implement it quite right, so the following works. I think I was forgetting the select in the INSERT INTO's.

CREATE OR REPLACE PROCEDURE MyTestProcedure(
    p_Endpoint Varchar(256),
    p_ContentType Varchar(200),
    MaxInProcess int = NULL)

LANGUAGE plpgsql

AS $body$

DECLARE
v_UtcNow timestamp(6);
v_ExpiredProcessing timestamp(6);

BEGIN
    SELECT CURRENT_TIMESTAMP into v_UtcNow at time zone 'utc';

    CREATE TEMP TABLE status_table(  
        Id bigint,
        Status smallint,
        CreatedAtUtc timestamp(6));

    WITH first_updated AS (UPDATE MyTable
        SET Status = 1
        WHERE UpdatedAtUtc < v_UtcNow
        RETURNING Id, Status, UpdatedAtUtc)

    INSERT INTO status_table
        SELECT Id, Status, UpdatedAtUtc 
        FROM first_updated;

    WITH m_result AS (UPDATE MyTable
        SET Status = 3
        WHERE ExpirationDateTimeUtc < v_UtcNow
        RETURNING Id, Status, UpdatedAtUtc)
    
    INSERT INTO status_table
        select Id, Status, UpdatedAtUtc 
        from m_result;
            
    DROP TABLE status_table;
    
END;
$body$