CODEWITHSUNDEEP
sqlpostgresql
Sujit
Sujit

Reputation: 2441

Dynamic column in SELECT statement postgres

I am quite new to the postgresql.

what is the best way to achieve this?

SELECT get_columns() 
  FROM table_name;

get_columns() will provide the column names for the query. I saw people advising to use EXECUTE statement but I couldn't got that working.

Lets say there is table Test with columns a,b,c and I want to run 

SELECT a,b FROM Test;
SELECT a,c FROM Test;

with column names generated dynamically.

Upvotes: 14

Views: 50948

Answers (5)

user457028
user457028

Reputation:

In order to write a dynamic query you would have to do something like:

EXECUTE 'SELECT '|| get_columns()|| ' FROM table_name' INTO results

Please read the documentation: http://developer.postgresql.org/pgdocs/postgres/plpgsql-statements.html

Upvotes: 10

Chris Travers
Chris Travers

Reputation: 26454

I think your biggest problem is that you need to return rows in a way that PostgreSQL can understand. This means basically, you can return a refcursor or you can return a consistent set of data types. I prefer the latter because it makes the system a bit more consistent from a programming perspective and there are some advanced directions you can take that but I can see the other way too.

Upvotes: 0

Scott Bailey
Scott Bailey

Reputation: 8286

You wont be able to use a function to generate a column list. And I really don't think this is the best way to approach the problem... That said, you can do it with 8.4 like so:

CREATE OR REPLACE FUNCTION dyn(p_name VARCHAR)
RETURNS SETOF RECORD AS
$$
  DECLARE
    p_sql  TEXT;
  BEGIN
   SELECT 'SELECT ' ||
     CASE p_name WHEN 'foo' THEN ' col1, col2, col3, col4 '
      WHEN 'bar' THEN 'col3, col4, col5, col6'
      WHEN 'baz' THEN 'col1, col3, col4, col6' END ||
   ' FROM mytest'
   INTO p_sql;
   RETURN QUERY EXECUTE p_sql;
  END
$$ LANGUAGE 'plpgsql';

Usage would be: SELECT * FROM dyn('foo') AS (one int, two int, three int, four int)

But honestly I'd suggest just making a view for each device.

Upvotes: 1

MkV
MkV

Reputation: 3096

Since you are using COPY FROM to a known large table, CREATE a FUNCTION which returns SETOF bigtable and SELECTs all the columns from the specific type, use NULL AS fieldname for the fields which are not required in that specific case, something like:

# \d SMALL
     Table "public.small"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 
 c      | integer | 
 d      | integer | 

# \d LARGE
     Table "public.large"
 Column |  Type   | Modifiers 
--------+---------+-----------
 a      | integer | 
 b      | integer | 
 c      | integer | 
 d      | integer | 

# CREATE OR REPLACE FUNCTION myData()
 RETURNS SETOF large LANGUAGE SQL AS $$
SELECT a, 
       CASE WHEN a = 1 
            THEN b 
       ELSE 
            NULL 
END as b, 
       CASE WHEN a = 2 
            THEN c 
       ELSE 
            NULL
END AS c, 
d
FROM small;
$$;

# SELECT * FROM mydata();
# COPY (SELECT * FROM myData()) TO STDOUT;

Obviously SQL might not be the best language to use, so PL/PgSQL or PL/Perl (or whatever) may be appropriate.

Upvotes: 0

Michał Niklas
Michał Niklas

Reputation: 54292

In such case I would use PL/pgSQL function using cursor.

Upvotes: 1

Related Questions