Dynamic Column in Select Statement Postgres

PostgreSQL: Select dynamic column in correlated subquery

You can use PL/pgSQL to dynamically request the columns. I'm assuming the following simplified database structure (all original and overide values are "character varying" in this example as I didn't find any further type information):

CREATE TABLE public.entity (
   id integer NOT NULL DEFAULT nextval('entity_id_seq'::regclass),   
   attr1 character varying,   
   attr2 character varying,   
   <...>
   CONSTRAINT entity_pkey PRIMARY KEY (id) 
)

CREATE TABLE public.attribute (   
   id integer NOT NULL DEFAULT nextval('attribute_id_seq'::regclass),
   name character varying,
   CONSTRAINT attribute_pkey PRIMARY KEY (id) 
)

CREATE TABLE public.override (   
   entity_id integer NOT NULL,
   attribute_id integer NOT NULL,
   value character varying,
   CONSTRAINT override_pkey PRIMARY KEY (entity_id, attribute_id),
   CONSTRAINT override_attribute_id_fkey FOREIGN KEY (attribute_id)
      REFERENCES public.attribute (id),
   CONSTRAINT override_entity_id_fkey FOREIGN KEY (entity_id)
      REFERENCES public.entity (id))

With the PL/pgSQL function

create or replace function get_base_value(
  entity_id integer,
  column_identifier character varying
)
returns setof character varying
language plpgsql as $$
declare
begin
  return query execute 'SELECT "' || column_identifier || '" FROM "entity" WHERE "id" = ' || entity_id || ';';
end $$;

you can use almost exactly your query:

SELECT
      OV.entity_id        as entity,
      AT.name             as attribute,
      OV.value            as value,
      ENT.get_base_value  as base_value
    FROM "override" AS OV
      LEFT JOIN "attribute" as AT
        ON (OV.attribute_id = AT.id)
      LEFT JOIN LATERAL (
        SELECT id, get_base_value FROM get_base_value(OV.entity_id, AT.name)
      ) AS ENT
    ON ENT.id = OV.entity_id;

select statement with dynamic columns for where condition

pg-promise doesn't include anything like this, because what you are looking for is a custom, and not particularly generic solution, because it may require AND/OR logic, type casting, use of nested properties, etc.

The library however, does give you all the tools necessary to create such a custom solution for yourself. For example, if all you need is AND condition for all properties in an object, you can use something like this:

const andProps = obj => ({
    rawType: true,
    toPostgres: () => Object.keys(obj).map(k => {
        const val = obj[k];
        if (val === null || val === undefined) {
            return pgp.as.format('$1:name IS NULL', [k]);
        }
        return pgp.as.format('$1:name = $2', [k, val]);
    }).join(' AND ')
});

The above code uses Custom Type Formatting, plus internal format function. And it adds special provision for null/undefined, to produce IS NULL. I used :name filter here, but you can also use :alias for shorter names (see SQL Names).

Usage Example

const obj = {
    id: null,
    name: 'John',
    age: 30
};

const data = await db.any('SELECT * FROM users WHERE $1', [andProps(obj)]);
//=> SELECT * FROM users WHERE "id" IS NULL AND "name" = 'John' AND "age" = 30

How to use dynamic column names in an UPDATE or SELECT statement in a function?

In an UPDATE statement in PL/pgSQL, the table name has to be given as a literal. If you want to dynamically set the table name and the columns, you should use the EXECUTE command and paste the query string together:

EXECUTE 'UPDATE ' || quote_ident(r.relname) ||
       ' SET ' || quote_ident(r.cols_list[1]) || ' = $1, ' || 
                  quote_ident(r.cols_list[2]) || ' = $2' ||
       ' WHERE ' || quote_ident(r.cols_list[1]) || ' = $3 AND ' ||
                    quote_ident(r.cols_list[2]) || ' = $4'
USING ncicd9, ncdesc, ocicd9, ocdesc;

The USING clause can only be used for substituting data values, as shown above.

Dynamic column alias from another column value in SELECT

It seems that there is no way to create the column alias dynamically without knowing the values since the beginning. As many commented the only way to achieve this kind of "table re-mapping" is to use the crosstab function.

Crosstab function summary

This function takes 2 arguments:

• The first one is a SQL statement that must return 3 columns:
  
  • The first column contains the values identifying each instance and that must be grouped in order to get the final result.
  • The second column contains the values that are used as categories in the final pivot table: each value will create a separate column.
  • The third column contains the values used to compile the new columns formed: for each category this column has the value of the instance that had the category value in the original table.
• The second argument is not mandatory and is a SQL statement that returns the distinct values the function should use as categories.

Example

In the example above we must pass a query to crosstab that:

• Returns as the first column the identifier of each final instance (in this case id)
• As second column the values used as categories (all values in key)
• As third column the values used to fill the categories (all values in value)

So the final query should be:

select * from crosstab(
    'select "id", "key", "value" from testTable order by 1, 2;',
    'select distinct "key" from testTable order by 1;'
) as result ("id" int8, "a" text, "b" text);

Since the crosstab function requires a column definition for the final pivot table, there is no way to determine the column alias dynamically.

Dynamically infer column names with client

A possible way to do that, with a PostgreSQL client, is to launch the second query we passed as argument to crosstab in order to retrieve the final columns and then infer the final crosstab query.

As an example, with pseudo-javascript:

const client;

const aliases = client.query(`select distinct "key" from testTable order by 1;`);

const finalTable = client.query(`select * from crosstab(
    'select "id", "key", "value" from testTable order by 1, 2;',
    'select distinct "key" from testTable order by 1;'
) as result ("id" int8, ${aliases.map(v => v + ' data_type').join(',')});`)

Useful articles

https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/

PostgreSQL query with dynamic number of columns

There are different methods used, some already mentioned here like crosstab. Also, you can build an own function that builds the query dynamically and returns as TABLE and few more methods.

But all require you to predefine an exact number of outputs and their data types.

If I understand your case that is something you would not want as you mentioned:

If we now need to start kicking the device each day, we need to update
the query.

Which is pretty much the same downside using crosstab and other ways.

So there is a way using Cursors. It is probably not the best way to go and if you can use crosstab then that is probably better.

But at least it is an option I'll add with comments in code.

Solution:

-- Function for opening cursor
CREATE OR REPLACE
FUNCTION    test_stats(
                c REFCURSOR,    -- cursor name
                sdate date,     -- start date of period wanted (included)
                edate date,     -- end date of period wanted (included)
                gtype text      -- you had in your 'tests' table some group type which I included just in case
            )
RETURNS     REFCURSOR
LANGUAGE    PLPGSQL
AS
$main$
BEGIN
    OPEN    c
    FOR
    -- Following dynamic query building can be
    -- used also if want to go with function that RETURNS TABLE
    EXECUTE format(
            '   SELECT  r.date,
                        %s
                FROM    test_results r
                WHERE   r.date BETWEEN %L AND %L
                GROUP BY 1
            ',
                -- Here we build for each 'name' own statement and 
                -- aggregate together with comma separator to feed
                -- into main query.
                -- P.S. We need to double check result unfortunately
                --      against test_results table once to get pre-filter
                --      for names in specified date range.
                --      With this we eliminate tests that for sure will
                --      not be presented in the range. In given test data
                --      this means eliminating 'hit'.
            (
                SELECT  string_agg(
                            DISTINCT format(
                                '(  SELECT  success
                                    FROM    test_results i
                                    WHERE   i.name = %1$L
                                    AND     i.date = r.date ) AS "%1$s"',
                                t.name
                            ),
                            ','
                        )
                FROM    tests t,
                LATERAL (   SELECT  array_agg( DISTINCT r.name )
                            FROM    test_results r
                            WHERE   r.date BETWEEN sdate AND edate
                        ) a( lst )
                WHERE   t.group = gtype     -- the group type is used here
                AND     t.name = ANY ( a.lst::text[] )
            ),
            sdate,      -- start date for between statement
            edate       -- end date for between statement
        );
    RETURN c;
END;
$main$;

-- Usage example:
BEGIN;
SELECT test_stats( 'teststats1', '2017-06-21'::date, '2017-06-23'::date, 'basic' );
FETCH ALL IN teststats1;
COMMIT;

-- Result (from your given test data set):
    date    | drop | poke | prod
------------+------+------+------
 2017-06-22 |      | t    | f
 2017-06-21 |      | t    | t
 2017-06-23 | t    | t    | t
(3 rows)

As I mentioned, it is not the perfect way, but it does the job :)

Get a dynamic column value in Activerecord Select statement

The "product_type" column isn't explicitly in the return value, but it´s there available for using it. If you execute Product.select(...).first.case you'll see its corresponding value.

If you want a different way to identify and access to it, you can use an alias:

Product.select("
  id,
  name,
  CASE
    WHEN product_type = 'AB' THEN 'xyz1'
    WHEN product_type = 'BC' THEN 'xyz2'
  END AS foo
").first.foo
# "xyz1"

---

Related Topics