On Tue, Dec 18, 2012 at 10:03 AM, Little, Douglas <douglas.lit...@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
