Re: evaluating expressions stored in table

On Tue, Dec 18, 2012 at 10:03 AM, Little, Douglas <DOUGLAS(dot)LITTLE(at)orbitz(dot)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