PL/TCL spi_exec insert problem
| От | Robert B. Easter |
|---|---|
| Тема | PL/TCL spi_exec insert problem |
| Дата | |
| Msg-id | 00061412064900.05623@comptechnews обсуждение исходный текст |
| Ответы |
Re: PL/TCL spi_exec insert problem
|
| Список | 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
В списке pgsql-general по дате отправления: