Home How to execute two queries separately?
Reply: 1

How to execute two queries separately?

Lesly O
1#
Lesly O Published in 2018-01-12 12:28:19Z

I have created a functions which inserts some rows in table my_table and return their identifier:

create function step_1() returns setof uuid as
$$
    insert into my_table(...) values(...) returning identifier;
$$
language sql volatile;

Now I want to use this function in another select query:

create function step_2() returns setof my_table as
$$
    select *
    from   my_table
    where  identifier in(select * from step_1());
$$
language sql volatile;

The problem here is that step_1() is never executed, because initially my_table does not consist any rows, so PostgreSQL is optimizing to not execute select * from step_1().

I think I need two things:

  • step_1() should always be executed, regardless of the contents of my_table.
  • step_1() should be executed as a separate query/context, so when step_2() is doing the select, it will actually see the inserted rows. The inserted rows should be present in the snapshot on which step_2() is executing.

How can I do this?

Clodoaldo Neto
2#
Clodoaldo Neto Reply to 2018-01-12 14:16:07Z
create table my_table (identifier uuid)
;
create function step_1() returns setof uuid as $$
    insert into my_table (identifier) values (gen_random_uuid())
    returning identifier;
$$ language sql volatile
;
create or replace function step_2() returns setof my_table as $$
    declare a_identifier uuid[];
    begin
    a_identifier := (
        select array_agg(identifier)
        from step_1() s (identifier)
    )
    ;
    return query
    select *
    from my_table
    where identifier = any (a_identifier)
    ;
    end;
$$ language plpgsql volatile
;
You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.358661 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO