Postgresql: cannot get result using sub-query at FROM ... place

Question

What I'm trying to accomplish is to get aggregated data for all unique combinations of sendercompid, targetcompid, msgtype through all tables in inner SQL.

I expect to have from 20mil to 40mil unique rows in resulting output.

I cannot succeed in running next query on Postgresql 8.3.13:

SELECT 
    sendercompid, targetcompid, count(msgtype), msgtype 
FROM 
    (SELECT table_name 
     FROM information_schema.tables 
     WHERE table_catalog = 'test' 
       AND table_schema = 'msg' 
       AND (table_name like 'fix_aee_20121214%') OR 
           (table_name like 'fix_aee2_20121214%')
   ) 
WHERE 
    (sendercompid LIKE '%201%') OR 
    (targetcompid LIKE '%201%') 
GROUP BY 
    sendercompid, targetcompid, msgtype ;

If this select is being split on 2 : outer and inner, then : inner will provide list of tables and outer will do select and grouping from each table .

If I run those two SQLs as one, I have an alias error from pgsql db

ERROR: subquery in FROM must have an alias

I tried use alias, but this error not disappear.

Any thoughts what I am missing there?

Thank you.

You cannot define the "from" part dynamically, all table names involved in a query must be known before the statement is executed. — user330315
– user330315, Commented Dec 17, 2012 at 14:35

DNS · Accepted Answer · 2012-12-17 14:28:14Z

1

FROM doesn't work the way you think it does. A sub-select works like any other query: it produces a set of rows. The outer SELECT works with those rows as though they were a table. There's no special magic beyond that; it has no idea that the values you're returning are table names, and won't treat them as such.

You can probably accomplish what you want using the catalog tables, but that would be complicated and hack-y.

Since your sub-tables appear to be date-based partitions, I think what you really want to use is the partitioning support built into Postgres, described in these docs. Essentially your partitions inherit from a parent table, and you set up range constraints on each child. When you query from the parent table with constraint_exclusion enabled, Postgres automatically selects the appropriate partition.

answered Dec 17, 2012 at 14:28

DNS

38.4k20 gold badges97 silver badges134 bronze badges

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

1 Comment

Daniel Gurianov Over a year ago

many thanks for detailed answer. Unfortunately , i do not have partitioning set up, and i cannot do so. DB is being filled with aplication out of my responsibility, so i can only read from tables and that is all. Indeed all tables in FROM clause are the same by data structure and differ only with date in name, so they could be childs of one master table. Unfortunatelly it is not the case.

Ihor Romanchenko · Accepted Answer · 2012-12-17 16:04:09Z

1

You can "run" such query using dynamic SQL. The idea - to form the proper query with table name as string inside PL\pgSQL procedure and EXECUTE it. Something like:

CREATE OR REPLACE FUNCTION public.function1 (
)
RETURNS TABLE (
  "field1" NUMERIC,
  "field2" NUMERIC,
  ...
) AS
$body$
BEGIN
 RETURN EXECUTE 'SELECT * FROM '|| (SELECT table_name from information_schema.tables 
              where table_catalog = 'test' AND 
                    table_schema='msg' AND
                    (table_name like 'fix_aee_20121214%') OR 
                    (table_name like 'fix_aee2_20121214%')); 
END;
$body$
LANGUAGE 'plpgsql';

Then use something like:

SELECT sendercompid, targetcompid, count(msgtype), msgtype 
       FROM  function1
       WHERE (sendercompid LIKE '%201%') OR 
             (targetcompid LIKE '%201%') 
       GROUP BY sendercompid, targetcompid, msgtype ;

Or you can create function with full query and some parameters to build WHERE clause.

Details: EXECUTE,

answered Dec 17, 2012 at 16:04

Ihor Romanchenko

29k9 gold badges56 silver badges45 bronze badges

3 Comments

Daniel Gurianov Over a year ago

Thanks Igor. Unfortunately this not work for me, i need part with "table_name like" dynamic. SO i will need to create new procedure for each date. This creates additional overhead.

Ihor Romanchenko Over a year ago

@Daniel No need to create a procedures for every date. Just pass the date as a parameter to the function. Something like table_name like 'fix_aee_'||to_char(date_param,'yyyymmdd')||'%'

Ihor Romanchenko Over a year ago

@Daniel Then SELECT * FROM function_name(CURRENT_DATE)

Wojtas · Accepted Answer · 2012-12-17 15:34:14Z

0

If this is just a concept then:

ERROR: subquery in FROM must have an alias.
There's is no information where columns sendercompid, targetcompid, count(msgtype), msgtype are come from.

SELECT sendercompid, targetcompid, count(msgtype), msgtype from ( SELECT table_name from information_schema.tables where table_catalog = 'test' AND table_schema='msg' AND (table_name like 'fix_aee_20121214%') OR (table_name like 'fix_aee2_20121214%') ) a WHERE (sendercompid LIKE '%201%') OR (targetcompid LIKE '%201%') GROUP BY sendercompid, targetcompid, msgtype ;

answered Dec 17, 2012 at 15:34

Wojtas

2,3641 gold badge17 silver badges9 bronze badges

Comments

Kanagaraj M · Accepted Answer · 2012-12-17 14:26:30Z

-1

Use alias for subquery. Better you alias for all tables to avoid confusion.

answered Dec 17, 2012 at 14:26

Kanagaraj M

9668 silver badges18 bronze badges

Collectives™ on Stack Overflow

Postgresql: cannot get result using sub-query at FROM ... place

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

3 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related