How to create a view of a query generated by another dynamic (meta) query

Question

How do I turn the query below into a temporary view? Everything I've tried returns the text of the generated query (second quoted code block). "Everything" includes...

Wrapping the 1st query in another SELECT * FROM...
Doing a CREATE TEMP VIEW as the 1st query, or as a select of the first query, or as just the text without any selects
A CREATE FUNCTION with the 1st query, then CREATE TEMP VIEW of the function

I can't really wrap it into a plpgsql function, because it's a dynamic query for a crosstab of a table containing an arbitrary number of rows, which crosstabs to an arbitrary number of columns. Thus, I can't define the return type for the plpgsql function.

The code below is taken directly from Erwin Brandstetter's answer to Transpose a table by converting columns to rows.

1st query:

SELECT 'SELECT *
FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
FROM   pg_attribute
WHERE  attrelid = 'tbl'::regclass
AND    attnum > 0
AND    NOT attisdropped
GROUP  BY attrelid;

Operating with attnum instead of actual column names. Simpler and faster. Join the result to pg_attribute once more or integrate column names like in the pg 9.3 example [of Erwin Brandstetter's answer to Transpose a table by converting columns to rows].

Generates a query of the form:

2nd Query:

SELECT *
FROM   crosstab(
   $ct$
   SELECT u.attnum, t.rn, u.val
   FROM  (SELECT row_number() OVER () AS rn, * FROM tbl) t
       , unnest(ARRAY[sl_no::text,username::text,designation::text,salary::text])
WITH ORDINALITY u(val, attnum)
   ORDER  BY 1, 2$ct$
   ) t (attnum bigint, r1 text, r2 text, r3 text, r4 text);

If you want a view that can show a different number of columns each time you select from it, based on the dynamic query - you can't have that. The structure of the view needs to be known at call time the same as the output of the function does. — Zegarek
– Zegarek, Commented Jul 3, 2024 at 17:26
@Zegarek That's not what I want, actually. This is a for a table that gets few updates that are done by IT staff and the row count is rather stable. So for an entire session, the select of the view would always have the same columns. The columns of the view are calculated in the 1st query by SELECT string_agg('r'|| rn ||' text', ', ') FROM (SELECT row_number() OVER () AS rn FROM tbl) t So what I want is to run the 1st query once at the beginning of the session, generate the 2nd query, CREATE TEMP VIEW once, and then use that temporary static view until logout. — Randall
– Randall, Commented Jul 3, 2024 at 17:45

Zegarek · Accepted Answer · 2024-07-04 07:13:24Z

1

run the 1st query once at the beginning of the session, generate the 2nd query, CREATE TEMP VIEW once, and then use that temporary static view until logout.

Wrap these in a procedure where you dynamically execute the output of that query.
Your clients can call this procedure once at the start of their session, let it set up/swap out the view, from which point they'll find the new structure under the static name my_view:
_{demo at db<>fiddle}

--create extension crosstab;
create table tbl(a text, b int);
insert into tbl select 1,1;

create procedure set_up_my_view() as $a$
DECLARE dynsql text;
BEGIN 
  SELECT INTO dynsql 
  'DROP VIEW IF EXISTS my_view;
   CREATE TEMP VIEW my_view AS 
   SELECT * FROM   crosstab(
       $ct$SELECT u.attnum, t.rn, u.val
        FROM  (SELECT row_number() OVER () AS rn, * FROM '
                              || attrelid::regclass || ') t
             , unnest(ARRAY[' || string_agg(quote_ident(attname)
                              || '::text', ',') || '])
                 WITH ORDINALITY u(val, attnum)
        ORDER  BY 1, 2$ct$
   ) t (attnum bigint, '
     || (SELECT string_agg('r'|| rn ||' text', ', ')
         FROM  (SELECT row_number() OVER () AS rn FROM tbl) t)
     || ')' AS sql
  FROM   pg_attribute
  WHERE  attrelid = 'tbl'::regclass
  AND    attnum > 0
  AND    NOT attisdropped
  GROUP  BY attrelid;

  EXECUTE dynsql; 
END $a$ language plpgsql;

Example client session:

call set_up_my_view();
select * from my_view;

attnum	r1
1	1
2	1

Adding something to show the structure change:

insert into tbl values (7,7);
call set_up_my_view();
select * from my_view;

attnum	r1	r2
1	1	7
2	1	7

insert into tbl values (9,9);
call set_up_my_view();
select * from my_view;

attnum	r1	r2	r3
1	1	7	9
2	1	7	9

answered Jul 4, 2024 at 7:13

Zegarek

29.9k5 gold badges27 silver badges32 bronze badges

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

1 Comment

Randall Over a year ago

Thank you! It felt like I should be able to do it, but I think I'd gotten so bleary-eyed I just couldn't see where my logic was wrong.(For one, I think I got flustered enough that I overlooked that a view created in a procedure/function persists beyond the END of the routine.) Thanks again!!

Collectives™ on Stack Overflow

How to create a view of a query generated by another dynamic (meta) query

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related