Postgresql stored procedure temporary table variable is not a known variable

Question

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.

Answer 1

https://www.postgresql.org/docs/14/plpgsql-statements.html#PLPGSQL-STATEMENTS-ASSIGNMENT1 Please refer the Tip section:

Tip Note that this interpretation of SELECT with INTO is quite different from PostgreSQL's regular SELECT INTO command, wherein the INTO target is a newly created table. If you want to create a table from a SELECT result inside a PL/pgSQL function, use the syntax CREATE TABLE ... AS SELECT.

based on this then you can do

CREATE TEMP TABLE  statustable AS (here is your query clause)

Maybe you can do update later. Another Point is as per manual, seems you cannot do CREATE Table by using CTE.

Each auxiliary statement in a WITH clause can be a SELECT, INSERT, UPDATE, or DELETE; and the WITH clause itself is attached to a primary statement that can also be a SELECT, INSERT, UPDATE, or DELETE.

https://www.postgresql.org/docs/current/queries-with.html

LukStorms's answer is pretty neat. But serval steps maybe more readable?

Answer 2

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$

Answer 3

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
);