0

Long story short, I have a function written by plpgsql in postgres, this function would do me a favor to generate a 'select * from crosstab.....' statement in the result so that I don't have to manually type the hundreds of column names for pivot table in this statement. So now if I just copy paste this statement in another query tool and run it, I can get the pivot table generated in the output. However, as you spotted, this process involves man work thus not automatic, I have tried some like EXECUTE function('which returns the statement I want'), but I have not yet found the solution...

In order to clarify what I am asking, here I can express in a very simple example.

select 1+1 returns 2 now i would love to use another key word i.e. EXECUTE 'select 1+1' to return 2 as well, how can I do this? I have been searching for a while, please help. Thanks.

Gin
  • 129
  • 2
  • 12
  • Related: [PL/pgSQL functions: How to return table with an execute statement](https://stackoverflow.com/q/18084936/190597). – unutbu Mar 15 '19 at 20:52
  • Related, but not so close to my purpose. Because the reason why I have that function is to let the function automatically created all the hundreds of columns for me, but in the link looks like I need to create another function which I would list all the hundreds of columns I need. – Gin Mar 15 '19 at 21:08
  • 1
    "If you want to return values from a dynamic SELECT query [you need to create a function](https://stackoverflow.com/a/10710523/190597)". – unutbu Mar 15 '19 at 21:16
  • Thanks for the hint, I am researching on it. – Gin Mar 15 '19 at 21:27

1 Answers1

0

Can you write your query to a file, for eg doit.sql? Then in the psql command line editor you can say

# \i doit.sql
Joe
  • 36
  • 3
  • I believe I would need everything in one place: create or replace function func():........; --then run this function result again right after – Gin Mar 15 '19 at 20:45