PostgreSQL Question posted in
The official documentation can be found here.

Macros/Metaprogramming in postgresql queries

In the case that I have the same example data as in this question and additionally declare the following two functions:

CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_price(_symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS float8
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS  -- !
$func$
SELECT p.price
FROM   example.price_table p
WHERE  p.symbol = _symbol
AND    p.time_of >= _time_of + _start
AND    p.time_of <= _time_of + _start + _duration
ORDER  BY p.time_of
LIMIT  1;
$func$;

CREATE OR REPLACE FUNCTION example.markout_666_example_666_price_table_666_volume(_symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS float8
  LANGUAGE sql STABLE STRICT PARALLEL SAFE AS  -- !
$func$
SELECT p.volume
FROM   example.price_table p
WHERE  p.symbol = _symbol
AND    p.time_of >= _time_of + _start
AND    p.time_of <= _time_of + _start + _duration
ORDER  BY p.time_of
LIMIT  1;
$func$;

These two functions are similar but reference different columns. In a more general case they might also reference different tables. I state two different functions however as inputting a column name (or a different table name) to a function seems to be regarded as an anti-pattern in writing postgres functions.

I can use both of these functions in a query like:

SELECT symbol, time_of, example.markout_666_example_666_price_table_666_price(symbol, time_of, '3 hours', '24 hours') as markout_price,
                        example.markout_666_example_666_price_table_666_price(symbol, time_of, '25 hours', '24 hours') as markout_price_2,
                        example.markout_666_example_666_price_table_666_volume(symbol, time_of, '3 hours', '24 hours') as markout_volume
from example.interesting_times it;

This is quite verbose however and we need to write symbol and time_of several times. If we have functions declared for more tables and more functions of these tables the queries can get quite complex.
Is it possible to instead write something like:

SELECT symbol, time_of, example.markout('example.price_table', 'price', '3 hours', '24 hours') as markout_price,
                        example.markout('example.price_table', 'price', '25 hours', '24 hours') as markout_price_2,
                        example.markout('example.price_table', 'volume', '3 hours', '24 hours') as markout_volume
from example.interesting_times it;

where example.markout is a macro/metaprogramming type construct and have this function be evaluated the same as if we used the more vebose syntax? Is there any metaprogramming-like technique that can be used here?

All I can find searching is sql_macro in oracle database and this page on "macro commands" in an out of date version of postgres which is no longer in the postgres manual.

Tags:

2

Answers


  1. 0

    You can use dynamic SQL to meet your needs as below:

    CREATE OR REPLACE FUNCTION example.markout(
    _tbl text, 
    _col text, 
    _symbol text, 
    _time_of timestamptz, 
    _start interval, 
    _duration interval
)
RETURNS float8
AS
$func$
DECLARE
    _stmt text;
    _result float8;
BEGIN
    _stmt = FORMAT(
        'SELECT p.%I
         FROM   %I p
         WHERE  p.symbol = %L
         AND    p.time_of >= %L + %L
         AND    p.time_of <= %L + %L + %L
         ORDER  BY p.time_of
         LIMIT  1;', 
         _col, _tbl, _symbol, _time_of, _start, _time_of, _start, _duration
    );

    RAISE NOTICE '%', _stmt;  -- For debugging

    EXECUTE _stmt INTO _result;  -- Fetch the result into a variable

    RETURN _result;  -- Return the fetched result
END;
$func$ LANGUAGE plpgsql STABLE STRICT PARALLEL SAFE;

    This can be invoked as:

    select  markout('example.price_table', 'price', symbol, time_of, '3 hours', '24 hours') as markout_price,
        markout('example.price_table', 'price', symbol, time_of, '25 hours', '24 hours') as markout_price_2,
        markout('example.price_table', 'volume', symbol, time_of, '3 hours', '24 hours') as markout_volume
from example.interesting_times it;

    E&EO

    Login or Signup to reply.
  2. 0

    Just create a single function, and select only the columns you need. Function inlining should work in your case, and then the unneeded columns will be pruned as usual. You just need to change it to RETURNS TABLE and remove STRICT.

    CREATE OR REPLACE FUNCTION example.price_table_666(_symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS TABLE
  LANGUAGE sql STABLE PARALLEL SAFE AS  -- !
$func$
SELECT
    p.price,
    p.volume
FROM   example.price_table p
WHERE  p.symbol = _symbol
AND    p.time_of >= _time_of + _start
AND    p.time_of <= _time_of + _start + _duration
ORDER  BY p.time_of
LIMIT  1;
$func$;

    You then use this in a FROM or a lateral JOIN, eg

    SELECT
  t.*,
  price1.price,
  price1.volume,
  price2.price AS price2
FROM someTable t
CROSS JOIN example.price_table_666(symbol, time_of, '3 hours', '24 hours') AS price1
CROSS JOIN example.price_table_666(symbol, time_of, '25 hours', '24 hours') AS price2;

    In a more genralized case of multiple tables, you can use a series of UNION ALLs with conditions, again if the parameter is a constant then the whole union will bre pruned to just the relevant arm.

    CREATE OR REPLACE FUNCTION example.price_table_666(_tableName text, _symbol text, _time_of timestamptz, _start interval, _duration interval)
  RETURNS TABLE
  LANGUAGE sql STABLE PARALLEL SAFE AS  -- !
$func$
SELECT p.*
FROM (
    SELECT
        p.price,
        p.volume
    FROM   example.price_table p
    WHERE  p.symbol = _symbol
    AND    p.time_of >= _time_of + _start
    AND    p.time_of <= _time_of + _start + _duration
    AND    _tableName = 'price'
    ORDER  BY p.time_of
    LIMIT  1
) p

UNION ALL
SELECT p.*
FROM (
    SELECT
        p.price,
        p.volume
    FROM   example.price_table2 p
    WHERE  p.symbol = _symbol
    AND    p.time_of >= _time_of + _start
    AND    p.time_of <= _time_of + _start + _duration
    AND    _tableName = 'price2'
    ORDER  BY p.time_of
    LIMIT  1
) p
;
$func$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search