evaluating expressions stored in table - Mailing list pgsql-general

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?

Thanks

Doug Little

Sr. Data Warehouse Architect | Business Intelligence Architecture | Orbitz Worldwide

500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 312.894.5164 | Cell 847-997-5741

Douglas.Little@orbitz.com

   orbitz.com | ebookers.com | hotelclub.com | cheaptickets.com | ratestogo.com | asiahotels.com