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.

Please edit your question and show us the complete code of the procedure. More often than not, you don't need a temp table to begin with when using Postgres — user330315
– user330315, Commented Dec 22, 2021 at 18:50
This is company code. I'll give it a try , but it will take a while. I come up with a more slimmed down version with the problem. Thanks @a_horse_with_no_name — Gabriel Kunkel
– Gabriel Kunkel, Commented Dec 22, 2021 at 18:51
If you want to return something, you should be using a function not a procedure anyway. — user330315
– user330315, Commented Dec 22, 2021 at 18:51
The return for the procedure is void. I'm using the temporary table to perform some work at the end. — Gabriel Kunkel
– Gabriel Kunkel, Commented Dec 22, 2021 at 18:53
SQL Server temporary tables as variables do not exist in plpgsql. — Stefanov.sm
– Stefanov.sm, Commented Dec 22, 2021 at 19:27

LukStorms · Accepted Answer · 2021-12-22 20:47:16Z

1

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

answered Dec 22, 2021 at 20:47

LukStorms

29.8k5 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Gabriel Kunkel Over a year ago

This is pretty neat. I'm going to try this one way or another.

LukStorms Over a year ago

You wouldn't expect it if you're coming from Sql Server, where the WITH has to be outside.

Gabriel Kunkel · Accepted Answer · 2021-12-22 20:56:08Z

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$

jian · Accepted Answer · 2022-01-04 15:58:19Z

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?

answered Jan 4, 2022 at 15:58

jian

5,0131 gold badge29 silver badges46 bronze badges

1 Comment

Gabriel Kunkel Over a year ago

Thanks so much! ...Yeah, there's ongoing battle between readability and expediency, but when it comes to readability and making things work... That's when the suffering begins. Haha.

Collectives™ on Stack Overflow

Postgresql stored procedure temporary table variable is not a known variable

UPDATE w/Examples

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

UPDATE w/Examples

3 Answers 3

2 Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related