CODEWITHSUNDEEP
postgresqldml
sirjoga
sirjoga

Reputation: 111

dummy dml after with's in plpgsql

Sometimes it is useful to use dml in with-blocks, e.g.

with 
  common_data_for_ins_and_upd as (
    select ...
  ),
  ins as (
     insert into some_table(...)
  ),
  upd as (
     update some_table set ...
  )
select

This works fine in sql, but trying to use this construct in plpgsql gives ERROR: query has no destination for result data.

Trying to replace select with perform gives syntax error at or near "perform".

Trying to frame it all in an outer perform gives WITH clause containing a data-modifying statement must be at the top level.

Of course, I could take the last dml-with block out of the with

with 
  ins as (
     insert into some_table(...)
  )
update some_table set ...

or write something like delete from some_table where false,

but maybe there is some more elegant solution?

upd. the select into construct works, but requires declaring a garbage variable.

Upvotes: 1

Views: 74

Answers (1)

Pepe N O
Pepe N O

Reputation: 2354

According to the error message, you just have to assign to a variable the returning result of select statement (since it always has a returning value, at least NULL), like this

do $$
declare res varchar;
begin
    with 
      ins as (
         insert into some_table(...)
      ),
      upd as (
         update some_table set ...
      )
    select into res;
    raise notice 'Returning value from select statement %', res;
end $$;

Other option, discarding the result from select statement (and avoid using a variable)

do $$
begin
    execute
    'with 
      ins as (
         insert into some_table(...)
      ),
      upd as (
         update some_table set ...
      )
    select';
    perform 1;  
end $$;

And using variables for query parameters

do $$
declare 
    value1 varchar;
    value2 varchar;
    value3 float;
begin
    execute (
    'with 
      ins as (
         insert into some_table values($1, $2, $3)
      ),
      upd as (
         update some_table set ...
      )
    select') using value1, value2, value3;
    perform 1;  
end $$;

Upvotes: 0

Related Questions