From: | Michael Moore <michaeljmoore(at)gmail(dot)com> |
---|---|
To: | postgres list <pgsql-sql(at)postgresql(dot)org> |
Subject: | is this a good approach? |
Date: | 2016-05-13 21:52:05 |
Message-ID: | CACpWLjOe-UWYg5aqSb2UaxiGbOfViX0S1bnCwK7LX6ORnZR3jQ@mail.gmail.com |
Views: | Whole Thread | Raw Message | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
I have a function that accepts about 20 input parameters. Based on the
values of these parameters a SELECT statement will be constructed. The
SELECT will be from two main tables, but based on parameters it might join
up to 3 additional tables. the WHERE clause will have many possibilities
too. The where clause will reference various input parameters. In other
words, it's a hot mess and only god knows what the SELECT statement will
look like by the time all the pieces are lashed together. The only thing
that won't vary is the column list. That is fixed.
All of the above is pretty much not up for redesign as I am duplicating
some functionality that exists in Oracle. One of the goals of my design is
to minimize the number of distinct cursors that are the result of cursor
parses. To that end I will be using the USING clause. For example
sql_select :=
> 'SELECT vendor_key::text rslt from tx_vendor where vendor_key = any
> ($1)';
> return query execute sql_select using v_blurb_type_codes;
The problem is, I will have a variable number of USING arguments with
differing datatypes depending on how the SELECT statement gets built.
So, my brilliant idea is to load the needed values into a JSONB where the
key to each of the jsonb values is a sequential number. It would look
something like:
> {"1":"good","2":"1234,4321,7787","3":"overtime"}
Then, when I go to execute the query it will look like
> case countOfParmsUsedInSQL
> when 1 then
> return query execute sql_select using json_vars::jsonb->>'1';
> when 2 then
> return query execute sql_select using
> json_vars::jsonb->>'1',json_vars::jsonb->>'2';
> when 3 then
> return query execute sql_select using
> json_vars::jsonb->>'1',json_vars::jsonb->>'2',json_vars::jsonb->>'3';
> end;
So, my questions are:
1) Will this work? I am concerned that I will have trouble with the
datatypes in jsonb. i.e. for example, sometimes parameter 2 might be a
NUMBER and other times it might be ::bigint[] (bigint[] would be used in
cases like $1 shown in my first example above.
2) Is there a better way to avoid string concatenation of parameters which
will result in thousands of hard parses? Oracle has dynamic binding
<https://oracle-base.com/articles/9i/dynamic-binds-using-contexts>
variables to help solve this problem.
I only mention this in case I have not explained adequately the nature of
the problem I am trying to solve.
TIA
Mike
From | Date | Subject | |
---|---|---|---|
Next Message | Michael Moore | 2016-05-13 23:52:13 | please ignore question 1 in my previous post |
Previous Message | Desmond Coertzen | 2016-05-13 14:43:35 | oracle_fdw |