Common Table Expression in PostgreSQL

Question

I am just learning the CTE and I want to create the dynamic query inside the WITH clause.

Below is what i have written code.

WITH name1 AS (
    SELECT schema_name as my_schema
    FROM public.schema_table
), name2 AS (
    SELECT num_rows as my_row
    FROM my_schema.row_table
)
SELECT my_row
from name2;

From the first query inside the WITH give number of schema exist in one database and that schema name return by

SELECT schema_name as my_schema 
FROM public.schema_table

I want to use in second query as I am saving it to my_schema.

but when i run this query then it gives me error like my_schema not exists that correct because I want to use the value my_schema contains.

How can you do that?

Chris Farmiloe · Accepted Answer · 2013-04-05 14:02:35Z

3

Firstly, you do not appear to have used name1 anywhere in your query, so I suspect you may not have understood WITH fully.

However it looks like you might have multiple tables called row_table each living in it's own schema and want to create a query that will let you choose which schema to fetch from.

FROM my_schema.row_table
     ^^^^^^^^^

my_schema is not a variable, it is the name of the schema. To do what you want you are going to have to use the pg_catalog tables to find the oid for the relation within the schema that you need and look up the information that way.

An alternative solution could be to manipulate the search path to do your bidding:

BEGIN;
    SET LOCAL SCHEMA 'my_schema';
    SELECT num_rows FROM row_table;   -- will use my_schema.
COMMIT;

edited Apr 5, 2013 at 14:02

answered Apr 5, 2013 at 12:37

Chris Farmiloe

14.3k5 gold badges51 silver badges57 bronze badges

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

3 Comments

Neel Patel Over a year ago

ok. got it. I will check... but as we can do this in plpgsql function with EXECUTE 'SELECT num_rows as my_row FROM || quote_literal(my_schema) || '.' || row_table; Can we do like that in CTE i mean in WITH clause ?

Neel Patel Over a year ago

Actuall just want to correct you. I want to use value in my_schema. Actually my_schema is not the schema name but value stored in my_schema that we have to use. So i think SET LOCAL SCHEMA my_schema also will fail. because we have to use values inside that like quote_literal(my_schema). Please correct me if i am wrong.

Chris Farmiloe Over a year ago

@ErwinBrandstetter it's an alias for set search_path... appeared in 8.4+

Collectives™ on Stack Overflow

Common Table Expression in PostgreSQL

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related