CODEWITHSUNDEEP
postgresql
jean
jean

Reputation: 1013

Use results from a query into the SELECT list

I would like to select all columns of a table except one.

My first step was to follow the example given in this answer. With something as such:

 SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum)
 FROM pg_attribute
 WHERE attrelid = 'public.my_table'::regclass
   AND NOT attisdropped
   AND attnum > 0
   AND attname <> 'bad_column'

This produces a string, as expected. But now I want to use this to query the rows of my table. If we call the result from above subset_columns, then I would like to make the query:

SELECT subset_columns
FROM my_table
LIMIT 10;

But I am not sure how to generate this subset_columns variable, and if this style of query is even possible in postgresql?

Upvotes: 1

Views: 917

Answers (1)

Yassine
Yassine

Reputation: 39

There is no such feature in PostgreSQL. You can do it only in pl/PgSQL (or other pl/*), but not in plain SQL and only if you want to store values on temporary table. Below you will find how you can create a variable pl/PgSQL:

WITH subset_columns AS (
      SELECT string_agg(quote_ident(attname), ', ' ORDER BY attnum)
       FROM pg_attribute
       WHERE attrelid = 'public.my_table'::regclass
       AND NOT attisdropped
       AND attnum > 0
       AND attname <> 'bad_column'
)

I suggest you to create a view instead of variable, and you select all the desired columns except the one you dont want to export. So it will be simple to re-use it whenever you want.

Upvotes: 2

Related Questions