Home > mailing lists

Thread: evaluating expressions stored in table

evaluating expressions stored in table

From

"Little, Douglas"

Date:

18 December 2012, 19:03:34

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

Attachment

image001.jpg

Re: evaluating expressions stored in table

From

Merlin Moncure

Date:

18 December 2012, 19:50:53

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

Re: evaluating expressions stored in table

From

Joe Conway

Date:

18 December 2012, 19:57:03

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

Re: evaluating expressions stored in table

From

Albe Laurenz

Date:

19 December 2012, 12:00:04

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