Postgresql – How to use value return by postgresSQL function in another query

postgresqlpostgresql-9.2

I am calling PostgreSQL function that returns me the text, How can I store this value in local variable and use the value/variable in another query. ?

Best Answer

It's not very clear what exactly you mean by a local variable - there is no such thing in SQL. In this case, just use the function in the place where you would put your variable (like a_horse_with_no_name suggested in his comment), like

SELECT important_thing FROM fancy_table WHERE it_thing_happened > now();

now() is a function, and illustrates the case nicely.

If you are about PL/pgSQL variables, you can do

[...]
my_timestamp := now();  -- not very meaningful IRL
[...]

Note that you can do this only inside a PL/pgSQL function or a DO block.

Last, if you mean a psql (that is, client-side) variable, you can use the \gset command:

test=# SELECT now() AS ts
test-# \gset i
test=# \echo :ts
2017-05-31 10:34:48.148068+02

Note that \gset is available from psql version 9.3 only.

Related Solutions

Postgresql – How to use array variable in query in PostgreSQL

You manage to get it wrong in just too many ways. To begin with, there is no need for dynamic SQL at all.

The best way to "solve this problem" is probably to start with the basics. Read the excellent manual about CREATE FUNCTION, PL/pgSQL and SQL functions.

A PL/pgSQL function could look like this (one of several variants):

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[], OUT xrc) AS
$func$
BEGIN
SELECT INTO xrc name FROM t1 WHERE xcheck @> xch; 
END
$func$ LANGUAGE plpgsql

Or, simpler as plain SQL function:

CREATE OR REPLACE FUNCTION fn_acgroup(xch varchar[])
  RETURNS text AS
$func$
SELECT name FROM t1 WHERE xcheck @> $1; 
$func$ LANGUAGE sql;

Or study some of the plpgsql examples that have been posted here.

PostgreSQL – Function to Execute Query, Manipulate and Return Result

You can use a cursor.

create type my_type as (id int, other_column text);

create or replace function get_data ()
returns setof my_type
language plpgsql as $$
declare
    cur cursor for select id, other_column from my_table limit 20000;
    rec record;
begin
    for rec in cur loop
        update other_table
            set status = 2 
            where id = rec.id;
        return next rec;
    end loop;
end $$;

select * from get_data();

Note however that the function will perform 20000 (maybe less) updates, not just one. It is not obvious if performance will be better or worse than in one query, as it depends on various circumstances. For example, one query should be much faster on an idle server whereas the function might turn out to be better on a heavily loaded server. The best way to check it is to test the function in the actual environment.

You can also use a temporary table. In this case rows are updated in one query.

create or replace function get_data_2 ()
returns setof my_type
language plpgsql as $$
begin
    create temporary table temp_table of my_type on commit drop;
    insert into temp_table
        select id, other_column from my_table limit 20000;
    update other_table 
        set status = 2
        where id in (select id from temp_table);
    return query select * from temp_table;
end $$;

Best Answer

Related Solutions

Postgresql – How to use array variable in query in PostgreSQL

PostgreSQL – Function to Execute Query, Manipulate and Return Result

Related Question