psql meta-command string interpolation differs from SQL

From: "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: psql meta-command string interpolation differs from SQL
Date: 2015-09-09 23:12:53
Message-ID: CAKFQuwYT3K8WcB8aTQvoJYYNWueA1oGO8yigrTc1n4_5e7uxKg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Mostly looking for affirmation though it was somewhat surprising that I had
no recollection of this dynamic even though I've been a fairly avid reader
of the documentation.

Executing the following in psql:

\set name_regexp '^\S+'
CREATE TABLE testtbl ( textid NOT NULL PRIMARY KEY CHECK (textid ~
:'name_regexp');

The constraint that ends up being saved is:

textid ~ '^S+'

This is a default 9.3 box. Typically I write my SQL literal regular
expressions without E'' in order to avoid escaping all of the
back-slashes. It seemed that using \set would be useful since I am using
psql to execute the DDL anyway.

psql --file ^^^

Any thoughts or suggestions?

At what point is the "\S" that I wrote being turned into an escaped "S"?
By the "\set" before going into the variable or at the :'name_regexp'
output conversion? Not that it likely matters to me, the end-user. It
would aid in deciding where it might be possible to introduce new syntax,
like the E'' that SQL uses, to gain a measure of conformity.

TIA

David J.

Browse pgsql-general by date

  From Date Subject
Next Message Michael Paquier 2015-09-09 23:14:46 Re: log_statement = 'mod' does not log all data modifying statements
Previous Message Christian Ramseyer 2015-09-09 21:54:26 GIN Trigram Index Size