evaluating expressions stored in table

On Tue, Dec 18, 2012 at 10:03 AM, Little, Douglas <DOUGLAS.LITTLE@orbitz.com

wrote:

Hi,****

** **

I need to evaluate an expression that I have stored in a table, and not
sure how to force evaluation of a column value.****

** **

Some background. This is a generic testing application that we’re using
to test source to target ETL’s.****

The specifics of the test expression depend on the actual tables
involved. Typicallly it’s been table_a.col_a = table_b.col_b but now
they want to use inequality or other operators.****

The tester’s define the test criteria in a table, then we use that
criteria to actually score the runtime results.****

** **

In my design I have 3 tables.****

Test – stores the test definition****

Testrun – stores the actual sql for a specific execution of a test****

Testscore – stores the actual values of the source and target values.
The scores are stored in different rows, with a common name to allow them
to be matched in the query. ****

** **

The pass/fail query looks something like this****

** **

Update testscore****

Set metricstatus = case when table_a.col_a = table_b.col_b then
‘PASS’ else ‘FAIL’ end****

..****

Where testrunid=x****

** **

I want to replace the table_a.col_a = table_b.col_b with the expression
stored in the test table and evaluate. ****

** **

I’m thinking – it’s dynamic sql, so I need to build the statement and then
evaluate using a function.****

** **

Anybody have any comments?

probably you need a pl/pgsql function which wraps your argument table,
builds the query, and invokes the query with EXECUTE.

beware sql injection.

merlin

On 12/18/2012 08:50 AM, Merlin Moncure wrote:

On Tue, Dec 18, 2012 at 10:03 AM, Little, Douglas
Anybody have any comments?

probably you need a pl/pgsql function which wraps your argument table,
builds the query, and invokes the query with EXECUTE.

For an example see slide 19 here:
http://www.joeconway.com/presentations/LISA2012-PredSrvAnalytics.pdf

beware sql injection.

good point!

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Douglas Little wrote:

I need to evaluate an expression that I have stored in a table, and not sure how to force evaluation
of a column value.

[...]

The pass/fail query looks something like this

Update testscore
Set metricstatus = case when table_a.col_a = table_b.col_b then 'PASS' else 'FAIL' end
..
Where testrunid=x

I want to replace the table_a.col_a = table_b.col_b with the expression stored in the test table and
evaluate.

I'm thinking - it's dynamic sql, so I need to build the statement and then evaluate using a function.

Anybody have any comments?

I would consider composing the statement in the application code:
SELECT the expression you need, compose the statement, send it
to the database.

If you need to do it on the database side, PL/pgSQL is your friend.
Since PostgreSQL 9.0 you can also use PL/pgSQL in the DO
SQL statement without having to define a function, but that will
probably not help if you need a return value.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general