-1

I'm looking to translate parsed structure to PostgreSQL. Hopefully, I am asking this correctly.

Is there code out there to do this already?

For more color, the need arose from this question/answer: https://dba.stackexchange.com/questions/162784/postgresql-translating-user-defined-calculations-into-executable-calculation-in

Note this part of the question: "Use an off-the-shelf solution that can translate the parsed structure to SQL. Most languages have something that can do this, like SQL::Abstract. If not, you gotta create it."

Edit: We are using PostgreSQL 9.3.5, if it matters.

Community
  • 1
  • 1
mountainclimber11
  • 1,339
  • 1
  • 28
  • 51

1 Answers1

0

Probably query is too complicate, but it does what do you need :-)

Parametrs: cmd - just your structure to parsing op - possible operations tables - jsonb object for translating table names from short form to full (probably you just mean than 'b' -> 'bbg' and 'p' -> 'pulls' instead 'bp' -> 'bbg_pulls'). I run this query on 9.6 and use jsonb. You can change it to just json for 9.3

WITH q AS (
     WITH param AS (
        SELECT '[bp2][-1]/[bp5]'::text AS cmd,
               '+-/%*'::text AS op,
               '{"bp": "bbg_pools"}'::jsonb AS tables
     ), precmd AS (
        SELECT btrim(replace(translate(cmd, '[]', ',,'), ',,', ','), ',') AS precmd
        FROM param
     ), split AS (
        SELECT i,
               split_part(precmd, ',', i) AS part
        FROM (
            SELECT generate_series(1, length(precmd) - length(translate(precmd, ',', '')) + 1) AS i,
                   precmd
            FROM precmd
            ) AS a
     ) SELECT *,
              CASE
                  WHEN part ~ ('^[' || op || ']$') THEN
                     ' ) ' || part || ' ( '
                  WHEN tables->>(translate(part, '0123456789', '')) != '' THEN
                     'select val from '::text || (tables->>(translate(part, '0123456789', '0')))  || ' where id = ' || translate(part, translate(part, '0123456789', '0'), '')
                  WHEN part ~ '^[-]?[0-9]*$' THEN
                     ' and val_date = (CURRENT_TIMESTAMP + (''' || part|| ' day'')::interval)::date '
                  ELSE
                     ' ERROR '
                  END AS res
       FROM param, precmd, split
       ORDER BY i
 )
 SELECT 'SELECT (' || string_agg(res, ' ') || ')'
 FROM q;

Some explanation (for better understanding you can try run query with SELECT * FROM q instead aggregating).

param CTE is just your paramters. In precmd I prepare cmd to split on parts and in split I do it.

Result of this query is:

SELECT (select val from bbg_pools where id = 2  and val_date = (CURRENT_TIMESTAMP + ('-1 day')::interval)::date   ) / (  select val from bbg_pools where id = 5)
Roman Tkachuk
  • 3,096
  • 1
  • 16
  • 15