4

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.

Improve this question
4
  • You can probably do something with composite types along with SETOF or arrays. Commented Jul 19, 2016 at 13:08
  • As Clodoaldo Neto has mentioned, there may be a way to rewrite your function's logic so that it doesn't need a temporary table at all. Editing your question to include more details of what the function does, perhaps in the form of a minimal reproducible example, would help people suggest such solutions. Commented Jul 19, 2016 at 13:36
  • 2
    I don't think you need a table (temporary or not) for this. What exactly do you want to store in that table? I think you are simply looking for generate_series(), e.g. generate_series(date_trunc('month', current_date - interval '3' month), current_date, interval '1' month) Commented Jul 20, 2016 at 6:49
  • Yes @a_horse_with_no_name that is exactly what I needed. Thanks I didn't know that function existed I just joined it to itself, which gave me both columns. This is so much neater then the way I wrote it. Commented Jul 20, 2016 at 8:06

3 Answers 3

Reset to default
3

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.

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

Comments

0

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.

Improve this answer

3 Comments

It's possible that the write restriction is not a matter of permissions. For instance, a replication slave is truly read-only as even the system catalogs are synchronised directly from the master.
Yes, that's quite possible.
Yes that is basically the issue it's a read-only window into our production DB so we can run diagnostics to see what people are doing on a daily basis.
-1

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()
Improve this answer

6 Comments

If I understand the question right, the OP wants a variable they can manipulate within their function, like a Temporary Table, rather than a one-off value.
@IMSoP You are probably right and if so he is probably getting it wrong. He needs to explain the real problem behind the question.
Getting what wrong? Unless you take the purist view that you shouldn't need functions with internal state, there seems no reason why a table-valued variable (or some roughly equivalent "set of records") should be any more "wrong" than a string-valued variable. (The purist position is defensible, but clearly not universal, or we wouldn't have pl/pgsql.)
@IMSoP By wrong I mean can't he use, say, a CTE or a subquery?
Sure, that's what I meant by "purist". The same could be said of CREATE TEMPORARY TABLE, which the OP is currently using - most uses could be replaced with something stateless. I think there's still potential for a direct answer to the question, though, since Postgres does have temporary tables, and table-valued variables do exist in other systems.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.