Обсуждение: [GENERAL] building extension with large string inserts
I am trying to build an extension where there is the need to insert large strings consisting of javascript code. The easiest way to get these string currently into a table is by using
\set varname `cat mycode.js`
INSERT INTO mytable VALUES (:'varname');
and run this from the psql client.
psql will escape the string nicely and stuff it into a text field.
This does not work with extensions since I cannot use \set anywhere else than the psql client.
What would be a proper way to get this code into a table via an extension? I've been working on generating INSERT statements for the extension's sql file but it seems a tedious job to escape the code myself.
Best,
Tom
Tom van Tilburg <tom.van.tilburg@gmail.com> writes:
> I am trying to build an extension where there is the need to insert large
> strings consisting of javascript code.
> ...
> What would be a proper way to get this code into a table via an extension?
> I've been working on generating INSERT statements for the extension's sql
> file but it seems a tedious job to escape the code myself.
Can't you use a dollar-quoted string? You just need to pick a delimiter
that doesn't appear anywhere in the javascript, say $JaVaScRiPt$ ...
regards, tom lane
I think I misunderstand. How would that help my insert statement?
You would get INSERT INTO mytable VALUES ($ javascript with a lot of unescaped characters like /n " // etc. $);
and: Am I correct that INSERTS are the way to go in extensions?
Best,
Tom vT.
On Wed, Jul 5, 2017 at 4:57 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tom van Tilburg <tom.van.tilburg@gmail.com> writes:
> I am trying to build an extension where there is the need to insert large
> strings consisting of javascript code.
> ...
> What would be a proper way to get this code into a table via an extension?
> I've been working on generating INSERT statements for the extension's sql
> file but it seems a tedious job to escape the code myself.
Can't you use a dollar-quoted string? You just need to pick a delimiter
that doesn't appear anywhere in the javascript, say $JaVaScRiPt$ ...
regards, tom lane
Tom van Tilburg <tom.van.tilburg@gmail.com> writes: > I think I misunderstand. How would that help my insert statement? > You would get INSERT INTO mytable VALUES ($ javascript with a lot of > unescaped characters like /n " // etc. $); Sure, but in a dollar-quoted literal you don't need to escape them. https://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-DOLLAR-QUOTING > and: Am I correct that INSERTS are the way to go in extensions? Seems reasonable, if you want the extension script to be self-contained. regards, tom lane
You are right! I totally forgot about this dollar quoting :)
Typically one of those things you will only remember the hard way ;-)
Thanks a lot,
Tom
On Wed, Jul 5, 2017 at 5:08 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Tom van Tilburg <tom.van.tilburg@gmail.com> writes:
> I think I misunderstand. How would that help my insert statement?
> You would get INSERT INTO mytable VALUES ($ javascript with a lot of
> unescaped characters like /n " // etc. $);
Sure, but in a dollar-quoted literal you don't need to escape them.
https://www.postgresql.org/docs/current/static/sql- syntax-lexical.html#SQL- SYNTAX-DOLLAR-QUOTING
> and: Am I correct that INSERTS are the way to go in extensions?
Seems reasonable, if you want the extension script to be self-contained.
regards, tom lane
I think I misunderstand. How would that help my insert statement?You would get INSERT INTO mytable VALUES ($ javascript with a lot of unescaped characters like /n " // etc. $);
Tom Lane provided the relevant syntax link, though if you supply an actual, shortened, example someone might show exactly what this all means for you - or how you'd need to tweak your text to make it work.
$$ { "key1": "value",
"key2": "value" } $$
Will be inserted as-is, explicit newlines and all. Likewise,
$$ { "key1": "value", \n "key2": "value" } $$
will be inserted without any newlines and with a literal "\n" in the middle of the text.
Unlike single-quote literals there is no alternate "E" form of dollar-quoting that will cause the \n to be interpreted as a newline. In practice its absence doesn't seem missed.
David J.