PostgreSQL – Set-Returning Functions

postgresql

I've been trying to use the generate_series() function of Postgres to obtain a a table like this:

I didn't arrived to a solution yet, so any ideas would be appreciated.

Best Answer

I guess something like this would do:

select x.n, y.n 
from generate_series(1,3) as x(n) 
cross join generate_series(1,4) as y(n)
order by y.n, x.n

The idea is to take the Cartesian product between a set of 3 elements and a set with 4 elements. In this case Z_3 and Z_4

Since 3 and 4 is co-prime ( gcd(3,4) = 1 ), we can conclude that the Cartesian product between Z_3 and Z_4 is isomorphic with Z_12 (if only we had formatting options ...). See zm-cross-zn-isomorphic-to-zmn for a proof. I.e we can use Z_12 and use modulo arithmetic to achieve the desired set:

select mod(x.n,3)+1, mod(x.n,4)+1 
from generate_series(1,12) as x(n) 
order by mod(x.n,4)+1, mod(x.n,3)+1

Related Solutions

Postgresql – Postgres RIGHT JOIN with custom array

You may do an OUTER JOIN directly with a list of values, like this:

SELECT v.id,tablename.* FROM tablename RIGHT JOIN (values (1),(3),...,(9)) as v(id)
  ON (tablename.field=v.id)

Implementing Postgres 9.4 Functions in Postgres 8.4

The pre-format solution used to be the good old concatenation, made safe by using quote_ident().

In your case, it would look like

...
    EXECUTE $$ 
        CREATE TABLE IF NOT EXISTS $$ || quote_ident(rows.tablename) || $$ (
             date date,
             number integer
        ) $$;
...

Note that here I am using dollar quoting when building the dynamic query. This allows me to use 'normal' syntax, as opposed to multiplicating single quotes, for example (not present in this example). This way most editors will highlight the statements nicely.

Another shortcoming of version 8.4 is that the CREATE TABLE IF NOT EXISTS syntax does not exist there - it was introduced with 9.1. You can still do a test for it:

IF EXISTS (SELECT 1 FROM pg_class c JOIN pg_namespace n ON c.relnamespace = n.oid WHERE relname = 'rows.tablename' AND nspname = 'your_schema')
THEN
    -- do the EXECUTE
END IF;

If you have only one schema around, you can omit the join to pg_namespace. pg_class and pg_namespace are part of the system catalogs - you may want to check out what else can be found there.

Best Answer

Related Solutions

Postgresql – Postgres RIGHT JOIN with custom array

Implementing Postgres 9.4 Functions in Postgres 8.4

Related Question