From: | "Robert B(dot) Easter" <reaster(at)comptechnews(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | PL/TCL spi_exec insert problem |
Date: | 2000-06-14 15:19:37 |
Message-ID: | 00061412064900.05623@comptechnews |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
I've been using the tables and functions below to store large amounts of text
(over the 8k limit) without any problems except for one: If the the text I'm
trying to store contains any single quote characters ('), then I get a parser
error somewhere in the process. I'm using php and the process works like this:
INSERT INTO largetext (lgtext) VALUES ('I\'d like to store this');
The lgtext (which could be more than 8k) contains a single quote that is escaped
with \. Php appears to automatically put the \ in. This insert is handled by
the trigger function largetext_trigfun(), which on INSERT does:
spi_exec "SELECT putlgtext(\'largetext_block\', $new_id, \'$NEW($2)\') AS rcode"
The putlgtext() function will break the lgtext ($NEW($2)) up into 7k blocks and
store them in the largetext_block table.
Somewhere in this, the code does not handle the single quote and gives:
PostgreSQL query failed: ERROR: parser error at or near "d"
Please help. :)
The solution is probably simple. I'm just not so good with TCL.
The largetext_trigfun() could easily be rewritten in PL/SQL if that would help.
=================================================
CREATE FUNCTION putlgtext (TEXT, INTEGER, TEXT) RETURNS INTEGER AS '
set i_table $1
set i_id $2
set i_t $3
set i_seq 0
while { $i_t != {} } {
set tblock [string range $i_t 0 7000]
set i_t [string range $i_t [expr 7000 + 1] end]
spi_exec "INSERT INTO $i_table (id, text_seq, text_block) VALUES ( $i_id , $i_seq , ''$tblock'' )"
incr i_seq
}
return 0
' LANGUAGE 'pltcl';
CREATE TABLE largetext (
id INTEGER PRIMARY KEY,
lgtext TEXT -- dummy field
);
CREATE TABLE largetext_block (
id INTEGER NOT NULL
REFERENCES largetext
ON DELETE CASCADE
ON UPDATE CASCADE,
text_seq INTEGER NOT NULL,
text_block TEXT,
PRIMARY KEY (id, text_seq)
);
CREATE FUNCTION largetext_trigfun() RETURNS OPAQUE AS '
switch $TG_op {
INSERT {
spi_exec "SELECT nextval(\'largetext_seq\') AS new_id"
set NEW($1) $new_id
spi_exec "SELECT putlgtext(\'largetext_block\', $new_id, \'$NEW($2)\') AS rcode"
if { $rcode != 0 } then { return SKIP }
}
UPDATE {
if { $NEW($2) != {} } then {
spi_exec "DELETE FROM largetext_text WHERE id = $OLD($1)"
spi_exec "SELECT putlgtext(\'largetext_block\', $OLD($1), \'$NEW($2)\') AS rcode"
if { $rcode != 0 } then { return SKIP }
}
}
}
set NEW($2) "ok"
return [array get NEW]
' LANGUAGE 'pltcl';
===============================================
--
Robert B. Easter
From | Date | Subject | |
---|---|---|---|
Next Message | J.R. Belding | 2000-06-14 15:30:36 | Re: Performance of PostgreSQL vs. Other DBs |
Previous Message | Bryan White | 2000-06-14 15:12:06 | Re: Performance of PostgreSQL vs. Other DBs |