use selected value as table name in postgres

Question

i've serarched for the answer, but did't find.

so i've got a table types

CREATE TABLE types
(
  type_id serial NOT NULL,
  type_name character varying,
  CONSTRAINT un_type_name UNIQUE (type_name)
)

which holds type names, lets say users - and this is the name of corresponding table users. this design may be a bit ugly, but it was made to allow users create their own types. (is there better way to acheve this?)

now i want to perform a query like this one:

select type_name, (select count(*) from ???) from types

to get list of all type names and count of objects of each type.

can this be done?

Erwin Brandstetter · Accepted Answer · 2012-02-03 09:08:21Z

6

You cannot do it directly in SQL

You can use a PLpgSQL function and dynamic SQL

CREATE OR REPLACE FUNCTION tables_count(OUT type_name character varying, OUT rows bigint)
RETURNS SETOF record AS $$
BEGIN
  FOR tables_count.type_name IN SELECT types.type_name FROM types
  LOOP
    EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(tables_count.type_name) INTO tables_count.rows;
    RETURN NEXT;
  END LOOP;
  RETURN;
END;
$$ LANGUAGE plpgsql;

SELECT * FROM tables_count();

edited Feb 3, 2012 at 9:08

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Feb 3, 2012 at 8:42

Pavel Stehule

46.6k6 gold badges103 silver badges102 bronze badges

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

2 Comments

Erwin Brandstetter Over a year ago

Ha, Pavel, you stole my thunder! Did not expect anybody else answering so soon. :) Obviously, I like your solution. You can tell that from what I came up with. Two small things: you were missing an OUT in the declaration and type_name is declared character varying which will raise an exception with your declared OUT parameter of type text. I fixed both.

k102 Over a year ago

thanks! i knew (or thought i knew) i can do it in plpgsql, i just thought how can it be done without it

Erwin Brandstetter · Accepted Answer · 2012-02-03 08:56:34Z

5

I don't have enough information, but I do suspect that something is off with your design. You shouldn't need an extra table for every type.

Be that as it may, what you want to do cannot be done - in pure SQL. It can be done with a plpgsql function executing dynamic SQL, though:

CREATE OR REPLACE FUNCTION f_type_ct()
  RETURNS TABLE (type_name text, ct bigint) AS
$BODY$
DECLARE
    tbl     text;
BEGIN
    FOR tbl IN SELECT t.type_name FROM types t ORDER BY t.type_name
    LOOP
        RETURN QUERY EXECUTE
        'SELECT $1, count(*) FROM ' || tbl::regclass
        USING tbl;
    END LOOP;
END;
$BODY$
  LANGUAGE plpgsql;

Call:

SELECT * FROM f_type_ct();

You'll need to study most of the chapter about plpgsql in the manual to understand what's going on here.

One special hint: the cast to regclass is a safeguard against SQLi. You could also use the more generally applicable quote_ident() for that, but that does not properly handle schema-qualified table names, while the cast to regclass does. It also only accepts table names that are visible to the calling user.

answered Feb 3, 2012 at 8:56

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

2 Comments

k102 Over a year ago

great! i understand whats going on in this function :) i thought i could do this without functions. extra table for every type is needed for performance reasons - small tables can be indexed more effective afaik

Erwin Brandstetter Over a year ago

@k102: BTW, indexes work just fine with big (or huge) tables. That's what they are built for. With small tables, sequential reads are often faster than using indexes at all.

Collectives™ on Stack Overflow

use selected value as table name in postgres

2 Answers 2

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related