hi, for the background, pls refer to this link. https://bitbucket.org/zzzeek/sqlalchemy/issue/3384/how-to-generate-complicated-nested-sql
I just follow Mike's suggestion, that is, only send sql templates and json/hstore/string/datetime objects to database instead of raw SQL statements. (if i understand it correctly. pls correct me if i am wrong) the stored procedure function(only for testing) and test script are: http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-QUOTE-LITERAL-EXAMPLE select test_upsert_operation('update app set name=%L where app_id=%L', VARIADIC ARRAY['upsert-sub-pgsql-again', '2']); CREATE OR REPLACE FUNCTION test_upsert_operation(update_exprssion text, variadic params text[]) RETURNS VOID AS $$ BEGIN -- execute format('update app set name=%L where app_id=%L', 'upsert-sub-pgsql', 2); execute format(update_exprssion, VARIADIC params); END; $$ LANGUAGE plpgsql; The problem is that 1. the number of objects sent to stored procedure is not fixed, 2. they have different data types, string/datetime/jsonb/hstore, etc. BUT variadic only accept the same type[1] (even if for anyarray, anyelment etc [2]) [1] http://www.postgresql.org/docs/9.4/static/xfunc-sql.html#XFUNC-SQL-VARIADIC-FUNCTIONS [2] http://www.postgresql.org/docs/current/static/extend-type-system.html Any tips for this function declaration so that we can pass objects to the stored procedure and do the formatting for escape literals??? thx again. wenlong -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
