From: | "Little, Douglas" <DOUGLAS(dot)LITTLE(at)orbitz(dot)com> |
---|---|
To: | "PostgreSQL General (pgsql-general(at)postgresql(dot)org)" <pgsql-general(at)postgresql(dot)org> |
Subject: | evaluating expressions stored in table |
Date: | 2012-12-18 16:03:19 |
Message-ID: | 8585BA53443004458E0BAA6134C5A7FBB401D7C1@EGEXCMB01.oww.root.lcl |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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(dot)Little(at)orbitz(dot)com<mailto:Douglas(dot)Little(at)orbitz(dot)com>
[cid:image001(dot)jpg(at)01CDDD06(dot)E43D5760] orbitz.com<http://www.orbitz.com/> | ebookers.com<http://www.ebookers.com/> | hotelclub.com<http://www.hotelclub.com/> | cheaptickets.com<http://www.cheaptickets.com/> | ratestogo.com<http://www.ratestogo.com/> | asiahotels.com<http://www.asiahotels.com/>
From | Date | Subject | |
---|---|---|---|
Next Message | Bruce Momjian | 2012-12-18 16:26:08 | Re: [GENERAL] trouble with pg_upgrade 9.0 -> 9.1 |
Previous Message | Christoph Berg | 2012-12-18 15:10:06 | Re: Moving some of Postgres off a SSD |