Thread: dollar-quoting trouble
Hi all,
I can't spot the trouble with this function definition:
create function dem.trf_null_empty_title()
returns trigger
language plpgsql
as $null_empty_title$
begin
if (NEW.title is null) then
return NEW;
end if;
if trim(NEW.title) <> '' then
return NEW;
end if;
NEW.title := NULL;
return NEW;
end;
$null_empty_title$;
PostgreSQL 8.1.8 (Debian/Etch) is telling me:
psql:dem-identity.sql:43: ERROR: unterminated dollar-quoted string at or near "$null_empty_title$
begin
if (NEW.title is null) then
return NEW;" at character 83
Can anyone help ?
Thanks,
Karsten
--
GPG key ID E4071346 @ wwwkeys.pgp.net
E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert wrote: > Hi all, > > I can't spot the trouble with this function definition: > > create function dem.trf_null_empty_title() > returns trigger > language plpgsql > as $null_empty_title$ > begin > if (NEW.title is null) then > return NEW; > end if; > > if trim(NEW.title) <> '' then > return NEW; > end if; > > NEW.title := NULL; > return NEW; > end; > $null_empty_title$; Works for me ... -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
Works for me on both 8.1 and 8.2. What client are you using? John On Apr 19, 2007, at 5:45 PM, Karsten Hilbert wrote: > I can't spot the trouble with this function definition: John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
On Thu, 19 Apr 2007 23:45:47 +0200, Karsten Hilbert <Karsten.Hilbert@gmx.net> wrote: > I can't spot the trouble with this function definition: > > create function dem.trf_null_empty_title() > returns trigger > language plpgsql > as $null_empty_title$ > begin > if (NEW.title is null) then > return NEW; > end if; > > if trim(NEW.title) <> '' then > return NEW; > end if; > > NEW.title := NULL; > return NEW; > end; > $null_empty_title$; > > > PostgreSQL 8.1.8 (Debian/Etch) is telling me: > > psql:dem-identity.sql:43: ERROR: unterminated dollar-quoted string at or near "$null_empty_title$ > begin > if (NEW.title is null) then > return NEW;" at character 83 Does "psql --version" match "select version()"? There's a message in the archive pgsql-bugs where this happened. klint. +---------------------------------------+-----------------+ : Klint Gore : "Non rhyming : : EMail : kg@kgb.une.edu.au : slang - the : : Snail : A.B.R.I. : possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---------------------------------------+-----------------+
On Fri, Apr 20, 2007 at 12:40:45PM +1000, Klint Gore wrote: >> I can't spot the trouble with this function definition: ... >> PostgreSQL 8.1.8 (Debian/Etch) is telling me: Actually, Lenny. >> psql:dem-identity.sql:43: ERROR: unterminated dollar-quoted string at or near "$null_empty_title$ >> begin >> if (NEW.title is null) then >> return NEW;" at character 83 > > Does "psql --version" match "select version()"? There's a message in > the archive pgsql-bugs where this happened. That was indeed part of the trouble: Both 7.4.16 and 8.1.8 being installed on Debian I couldn't get the default psql be 8.1 despite using update-alternatives. So there was psql 7.4 being used against the 8.1 server which is bound to lead to trouble. That being solved by deinstalling 7.4 (which I was transitioning off anyways) psql created the function just fine. My own bootstrapper code still throws the error (it is using psycopg2 which is using libpq4) ... but, wait, deinstalling libpq3 apparently makes psycopg2 use libpq4 (?!) as it now works ... Thanks for the assistance from the GNUmed team, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Karsten Hilbert wrote: >> Does "psql --version" match "select version()"? There's a message in >> the archive pgsql-bugs where this happened. > That was indeed part of the trouble: > > Both 7.4.16 and 8.1.8 being installed on Debian I couldn't > get the default psql be 8.1 despite using > update-alternatives. So there was psql 7.4 being used > against the 8.1 server which is bound to lead to trouble. > > That being solved by deinstalling 7.4 (which I was > transitioning off anyways) psql created the function just > fine. If you do want 2 versions installed (both in different prefix dirs) at the same time then you probably want to make sure that your shell $PATH setting includes the path to the newer version of psql and then specify the full path to the older version of psql when you want to use the older version. Or just use the full path every time you run psql. -- Shane Ambler pgSQL@Sheeky.Biz Get Sheeky @ http://Sheeky.Biz
On Fri, Apr 20, 2007 at 06:08:36PM +0930, Shane Ambler wrote: > If you do want 2 versions installed (both in different prefix dirs) at > the same time then you probably want to make sure that your shell $PATH > setting includes the path to the newer version of psql and then specify > the full path to the older version of psql when you want to use the > older version. Or just use the full path every time you run psql. Thanks, I knew that much from following the list. However, I was under the impression that - on Debian - by using update-alternatives I could set the "default" PG to the 8.1 install. Which apparently wasn't the case. Thanks for your answer, though, Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346