PL/TCL spi_exec insert problem

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

Responses

Browse pgsql-general by date

  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