Postgresql Table Variable

PostgreSQL table variable

Use a temporary table in PostgreSQL. For your example:

CREATE TEMP TABLE product_totals (
   product_id int
 , revenue money
);

The manual about CREATE TABLE:

If specified, the table is created as a temporary table. Temporary
tables are automatically dropped at the end of a session, or
optionally at the end of the current transaction (see ON COMMIT
below). The default search_path includes the temporary schema first
and so identically named existing permanent tables are not chosen for
new plans while the temporary table exists, unless they are referenced
with schema-qualified names. Any indexes created on a temporary table
are automatically temporary as well.

Unlogged tables in Postgres 9.1 or later are a somewhat related feature. They save disk writes by not writing to WAL. Here is a discussion of the features by Robert Haas:

Aside, concerning the money data type:

• PostgreSQL: Which Datatype should be used for Currency?

Postgresql stored procedure temporary table variable is not a known variable

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$

Select from a table variable

There are no "table variables" in plpgsql. That's something you would find in SQL Server.

Use a temporary table instead:

BEGIN

CREATE TEMP TABLE table_holder AS
SELECT * FROM table_holder
WHERE <some condition>
ORDER BY <some expression>
;

...

END

A temporary table exists for the lifetime of a session. To drop it at the end of the function (or an enclosing transaction) automatically, use ON COMMIT DROP in the creating statement.

CREATE TEMP TABLE table_holder ON COMMIT DROP AS
SELECT ...

The temporary table is visible in the same session, but not outside.

One alternative would be to use cursors in PL/pgSQL.

More alternatives:

• SELECT multiple rows and columns into a record variable

FOR loop with the table name as a variable

Uncomment this line

EXECUTE 'select count(*) from' || quote_ident(dataTable);

Add space between from and single quote and change the query to

EXECUTE 'select count(*) from ' ||dataTable INTO countData;

Comment the line below

--select count(*) into countData from quote_ident(dataTable);

The reason you get 1 every time because in your query, if you pass any string variable in quote_indent() it will return 1 try this execute 'SELECT count(*) FROM '||quote_ident('xyz'); and the result will be the same, this means instead of treating the value in a variable as a table name it is treating it as a plain string only.

Declare a Table as a variable in a stored procedure?

You can iterate over the result of the dynamic query directly:

create or replace function gapandoverlapdetection ( table_name text, entity_ids bigint[])  
  returns table (entity_id bigint, valid tsrange, causes_overlap boolean, causes_gap boolean)
as $$
declare 
    var_r record;
begin   
  for var_r in  EXECUTE format('select entity_id, valid 
                                from %I 
                                where entity_id = any($1)
                                  and registration > now()::timestamp 
                                order by valid ASC', table_name) 
                    using entity_ids
  loop
   ... do something with var_r

    -- return a row for the result
    -- this does not end the function
    -- it just appends this row to the result
    return query 
      select entity_id, true, false; 
  end loop;
end 
$$ language plpgsql;

The %I injects an identifier into a string and the $1 inside the dynamic SQL is then populated through passing the argument with the using keyword

How to assign result of table function to variable in PL/pgSQL

Postgres doesn't support table's variables. If you can pass a some relational content, then a) you can use a temporary table or b) you can pass a array of composite values:

CREATE TYPE branch_type AS 
(
  object_id int,
  parent_id int,
  name      text
)

CREATE OR REPLACE FUNCTION build_org_branch(IN p_org_id organization.org_id%type,
                                            IN p_padding text)
  RETURNS branch_type[] AS ...

and then you can write

declare
  l_table branch_type[];
begin
  l_table := build_org_branch(1, ' ');
  if l_table is not null then
    -- do stuff with table rows
  end if;
end;

This is array to array assignment. Table to array is possible too, but always it has to be static typed.

CREATE OR REPLACE FUNCTION build_org_branch(IN p_org_id organization.org_id%type,
                                            IN p_padding text)
  RETURNS SETOF branch_type AS ...

and processing:

declare
  l_table branch_type[];
begin
  l_table := ARRAY(SELECT build_org_branch(1, ' '));
  if l_table is not null then
    -- do stuff with table rows
  end if;
end;

For smaller number of rows (to ten thousand) a arrays should be preferred. For high number of rows you should to use temp table.

How to insert declared type variable into table | Postgress

1. There is no table type, there is only row composite type. Check manual 43.3.4. Row Types.
2. use row type.

---

create or replace procedure student_create(p_code text)
language plpgsql
as $$
declare
   v_student public.student
begin
    for v_student in  select *  from student where code = p_code and is_latest_version is true
    loop
    v_student.id = uuid_generate_v4();
    v_student.version_created_at = now();
    v_student.version_updated_at = v_student.version_created_at;
    v_student.is_latest_version = true;
    v_student.code = p_code;
    INSERT INTO student VALUES(v_student.*);
end loop;
end;$$;

call it: call student_create('hello');

3. use update clause directly.

create or replace procedure student_create_1(p_code text)
language plpgsql as $$
BEGIN
    with a  as ( select uuid_generate_v4() as id ,
                       now() as version_created_at,
                       now() as version_updated_at,
                       p_code as "code"   from student 
                where code = p_code and is_latest_version is true)
    
     INSERT INTO student(id, version_created_at, version_updated_at, code) 
            select a.id, a.version_created_at,a.version_updated_at,a."code" from a;
    
end
$$;

call it: call student_create_1('hello');

fiddle code: here

---

Related Topics