CODEWITHSUNDEEP
postgresql
jgerstle
jgerstle

Reputation: 1684

Is there a way to create a table variable in postgresql

I have created a function that creates a temporary table and inserts into the table. The problem is that I need this to work in a read-only instance as well, but in a read-only instance I can't create a table and/or insert into it. Is there any other way of doing this? Maybe by creating a table variable in a way similar to other SQL languages?

I did some research and there doesn't seem to be a table variable, but maybe an array of records? Any ideas?

UPDATE:
To answer people's questions, I am trying to create a function that returns a list of dates from now until x intervals ago in intervals of y.

So for instance, select * from getDates('3 days', 1 day') returns:

 startdate  |  enddate   
------------+------------
 2016-07-20 | 2016-07-21
 2016-07-19 | 2016-07-20
 2016-07-18 | 2016-07-19

And select * from getDates('3 months', '1 month'); returns:

 startdate  |  enddate   
------------+------------
 2016-07-01 | 2016-08-01
 2016-06-01 | 2016-07-01
 2016-05-01 | 2016-06-01

I currently do this by using a while loop and going back per interval until I hit the time given by the first parameter. I then insert those values into a temp table, and when finished I select everything from the table. I can include the code if necessary.

Upvotes: 4

Views: 11770

Answers (3)

IMSoP
IMSoP

Reputation: 97688

You can create a permanent named Composite Type representing the structure of your temporary table, and then use an array variable to manipulate a set of rows inside a function:

-- Define columns outside function
CREATE TYPE t_foo AS
(
  id int,
  bar text
);

CREATE OR REPLACE FUNCTION test()
  RETURNS SETOF t_foo AS
$BODY$
    DECLARE
        -- Create an empty array of records of the appropriate type
        v_foo t_foo[] = ARRAY[]::t_foo[];  -- correction    
    BEGIN
        -- Add some rows to the array
        v_foo := v_foo || ( 42, 'test' )::t_foo;
        v_foo := v_foo || ( -1, 'nothing' )::t_foo;

        -- Convert the array to a resultset as though it was a table
        RETURN QUERY SELECT * FROM unnest(v_foo);
    END;
$BODY$
  LANGUAGE plpgsql;
 
SELECT * FROM test();

The crucial part here is the variable of type t_foo[] - that is, an array of records of the pre-defined type t_foo.

This is not as easy to work with as a temporary table or table variable, because you need to use array functions to get data in and out, but may be useful.

It's worth considering though whether you really need the complex local state, or whether your problem can be re-framed to use a different approach, e.g. sub-queries, CTEs, or a set-returning function with RETURN NEXT.

Upvotes: 3

NovaDenizen
NovaDenizen

Reputation: 5305

Maybe the best way to approach it is to get your administrator to GRANT TEMPORARY ON DATABASE database_name TO the user account performing your actions. You still will only have read-only access to the database.

Upvotes: 0

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125204

Declare the function as retuning table

create function f()
returns table (
    a int,
    b text
) as $$
    select x, y from t;
$$ language sql;

Use it as:

select *
from f()

Upvotes: -1

Related Questions