CODEWITHSUNDEEP
sqlpostgresql
ZedZip
ZedZip

Reputation: 6470

PostgreSQL: How to add dynamic query as a column to query?

My query:

select table_name,column_name,seq_name,nextval(seq_name::TEXT)
from 
(
  SELECT table_name,column_name,SUBSTRING(column_default,
  strpos(column_default,'(')+2,
  strpos(column_default,'::')-1 -(strpos(column_default,'(')+2)) as seq_name
  FROM information_schema.columns 
  WHERE column_default LIKE 'nextval%'
) t;

How I can add to the query yet another column which calculate max from the current table? Like this:

(select coalesce(max(fname_id),1) from <current table>) as realmax

The problem is that table name in the current row is a string

Upvotes: 0

Views: 101

Answers (2)

Vao Tsun
Vao Tsun

Reputation: 51466

You can create function:

create or replace function ms(_t text, _a text) returns int as $$
declare
 _m int;
begin
  execute format('select coalesce(max(%I),1) from %I',_a,_t) into _m;
 return _m;
end;
$$ language plpgsql;

and use it:

select table_name,column_name,seq_name,nextval(seq_name::TEXT)
from
(SELECT table_name,column_name,SUBSTRING(column_default,
strpos(column_default,'(')+2,
strpos(column_default,'::')-1 -(strpos(column_default,'(')+2)) as seq_name
FROM information_schema.columns WHERE column_default LIKE 'nextval%') t;

Also mind you don't operate with schema names here. And it might be needed

Upvotes: 1

user330315
user330315

Reputation:

This can be done without dynamic SQL:

select table_name,column_name, nextval(seq_name), 
       (xpath('/table/row/max/text()', xml))[1]::text::int as max_val
from (
  SELECT table_name,
         column_name,
         table_schema,
         query_to_xml(format('select coalesce(max(%I),1) as max from %I.%I', column_name, table_schema, table_name), false, false, '') as xml
  FROM information_schema.columns 
  where column_default LIKE 'nextval%'
) t;

That's a variation of my answer here

Upvotes: 0

Related Questions