Re: [GENERAL] How can I interpolate psql variables in function bodies? - workaround
I've found a workaround using the new pg 8.3 feature of default values for function arguments. It is not a completely general workaround (it won't, e.g. allow type or table names to be interpolated) but it does what I need and perhaps others may find this trick useful. To briefly recap the problem: On Mon, 2009-06-15 at 15:28 -0700, J. Greg Davidson wrote: Hi dear colleagues, When I need such a constant in a function it is not substituted: $ psql -v foo=10 # create function foo() returns integer as 'select '(:foo) language sql; ERROR: syntax error at or near ( at character 51 The workaround: CREATE FUNCTION foo(integer DEFAULT :foo) RETURNS integer AS $$ SELECT $1 $$ LANGUAGE sql; In other words, I add an extra DEFAULT argument for each psql variable I need to use in the function body. I'm wondering if there is any advantage in psql's declining to do substitutions inside of quotes, especially $$ oxford quotes $$. Perhaps a future version of psql can make things easier. _Greg J. Greg Davidson -- 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] How can I interpolate psql variables in function bodies?
On Tue, 2009-06-16 at 09:20 +0200, Albe Laurenz wrote: I came up with the following: psql -v foo=10 test= \set funcbody `echo 'SELECT\ `:foo`echo '` test= create function foo() returns integer as :funcbody language sql; But that is ugly as hell and depends on the underlying operating system to have a Bourne-like shell. Yours, Laurenz Albe Here is a similar trick: psql -v f=10 \set g '''select ' :f '::integer;''' create function g() returns integer as :g language sql immutable; g() can be used in the bodies of other functions where it should be inline substituted since it's immutable. I'm concerned as to whether this might break if psql slightly changes how it it does substitution. The documentation does not fully specify how substitution behaves. _Greg J. Greg Davidson -- 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] How can I interpolate psql variables in function bodies?
J. Greg Davidson wrote: Hi dear colleagues, I'm trying to pull some platform-specific constants out of my code by using psql variables, e.g.: $ psql -v TypeLength=4 # CREATE TYPE tref ( INTERNALLENGTH = :TRefTypeLength, INPUT = tref_in, OUTPUT = tref_out, PASSEDBYVALUE ); which works fine, but when I need such a constant in a function it is not substituted. A simplified example: $ psql -v foo=10 # select :foo; ?column? -- 10 (1 row) # create function foo() returns integer as 'select '(:foo) language sql; ERROR: syntax error at or near ( at character 51 I'm sure that I could do something horrible by using EXECUTE inside of a plpgsql function, and I'm hoping that someone will have a simpler alternative. For example, is there some kind of quoting mechanism I can use which will not impede psql from doing substitutions? I don't think that there is a convenient way, as variables are not substituted inside string constants. I came up with the following: psql -v foo=10 test= \set funcbody `echo 'SELECT\ `:foo`echo '` test= create function foo() returns integer as :funcbody language sql; But that is ugly as hell and depends on the underlying operating system to have a Bourne-like shell. 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
[GENERAL] How can I interpolate psql variables in function bodies?
Hi dear colleagues, I'm trying to pull some platform-specific constants out of my code by using psql variables, e.g.: $ psql -v TypeLength=4 # CREATE TYPE tref ( INTERNALLENGTH = :TRefTypeLength, INPUT = tref_in, OUTPUT = tref_out, PASSEDBYVALUE ); which works fine, but when I need such a constant in a function it is not substituted. A simplified example: $ psql -v foo=10 # select :foo; ?column? -- 10 (1 row) # create function foo() returns integer as 'select '(:foo) language sql; ERROR: syntax error at or near ( at character 51 I'm sure that I could do something horrible by using EXECUTE inside of a plpgsql function, and I'm hoping that someone will have a simpler alternative. For example, is there some kind of quoting mechanism I can use which will not impede psql from doing substitutions? Thanks, _Greg -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
