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?
On Tue, Dec 18, 2012 at 10:03 AM, Little, Douglas <DOUGLAS.LITTLE@xxxxxxxxxx> wrote:
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
