Re: [GENERAL] evaluating expressions stored in table
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_bthen '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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] evaluating expressions stored in table
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_bthen ‘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: [GENERAL] evaluating expressions stored in table
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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
