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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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 anextension?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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-QUOTINGand: 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
On Wed, Jul 5, 2017 at 8:04 AM, Tom van Tilburg <tom.van.tilburg@gmail.com>
wrote:
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.