CODEWITHSUNDEEP
sqlpostgresql
k102
k102

Reputation: 8089

use selected value as table name in postgres

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?

Upvotes: 5

Views: 4589

Answers (2)

Pavel Stehule
Pavel Stehule

Reputation: 45950

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();

Upvotes: 6

Erwin Brandstetter
Erwin Brandstetter

Reputation: 659327

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.

Upvotes: 5

Related Questions