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

Question

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

Vao Tsun · Accepted Answer · 2018-01-26 10:55:25Z

1

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

edited Jan 26, 2018 at 10:55

answered Jan 26, 2018 at 10:02

Vao Tsun

52.4k13 gold badges114 silver badges149 bronze badges

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

Comments

score 0 · Accepted Answer · 2018-01-26 10:49:37Z

0

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

edited Jan 26, 2018 at 10:49

answered Jan 26, 2018 at 10:08

user330315

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related