Re: PL/TCL spi_exec insert problem

On Wed, 14 Jun 2000, Robert B. Easter wrote:
> 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.

[snip]

Well, like I thought, the fix is simple - use regsub to re-escape the quote
characters. For anyone who might have been interested, below is the fixed
functions. They will now take the quote character in the text with no
problems. Only one problem might remain: If a block gets cut right inbetween
\'. But that would be rare.

-- Load the PGSQL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION plpgsql_call_handler()
RETURNS OPAQUE AS '/usr/local/pgsql/lib/plpgsql.so'
LANGUAGE 'C';

CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql'
HANDLER plpgsql_call_handler
LANCOMPILER 'PL/pgSQL';

--------------------------------------------------------------------------------
--
-- Large Text storage
--

-- putlgtext - generic function to store text into the
-- specified text storage table.
-- The table specified in $1 should have the following
-- fields:
-- id, text_seq, text_block
--
-- $1 is the name of the table into which $3 is stored
-- $2 is the id of the text and references id in another table
-- $3 is the text to store, which is broken into chunks.
-- returns 0 on success
-- nonzero otherwise
CREATE FUNCTION putlgtext (TEXT, INTEGER, TEXT) RETURNS INTEGER AS '

set i_table $1
set i_id $2
set i_t {}
regsub -all {[\\'']} $3 "\\''\\''" i_t
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';

-- getlgtext - like putlgtext, this is a generic
-- function that does the opposite of putlgtext
-- $1 is the table from which to get TEXT
-- $2 is the id of the text to get
-- returns the text concatenated from one or more rows
CREATE FUNCTION getlgtext(TEXT, INTEGER) RETURNS TEXT AS '
set o_text {}
spi_exec -array q_row "SELECT text_block FROM $1 WHERE id = $2 ORDER BY text_seq" {
append o_text $q_row(text_block)
}
return $o_text
' LANGUAGE 'pltcl';

-- largetext exists just to hold an id and a dummy 'lgtext' attribute.
-- This table's trigger function provides for inserting and updating
-- into largetext_block. The text input to lgtext actually gets
-- broken into chunks and stored in largetext_block.
-- Deletes to this table will chain to largetext_block automatically
-- by referential integrity on the id attribute.
-- Selects have to be done using the getlgtext function.
CREATE TABLE largetext (
id INTEGER PRIMARY KEY,
lgtext TEXT -- dummy field
);
COMMENT ON TABLE largetext IS 'Holds large text';

-- This table must have the field names as they are.
-- These attribute names are expected by put/getlgtext.
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)
);
COMMENT ON TABLE largetext_block IS 'Holds blocks of text for table largetext';
CREATE SEQUENCE largetext_seq;

-- SELECT:
-- SELECT id AS the_id FROM largetext;
-- SELECT getlgtext('largetext_block', id) FROM largetext WHERE id = the_id;

-- INSERT:
-- INSERT INTO largetext (lgtext) values ('.......');

-- DELETE:
-- DELETE FROM largetext WHERE id = someid;
-- deletes from largetext and by referential
-- integrity, from largetext_text all associated block rows.
CREATE FUNCTION largetext_trigfun() RETURNS OPAQUE AS '
set i_t {}
regsub -all {[\\'']} $NEW($2) "\\''\\''" i_t

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, ''$i_t'') 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';

-- Set the function as trigger for table largetext
CREATE TRIGGER largetext_trig BEFORE INSERT OR UPDATE
ON largetext FOR EACH ROW EXECUTE
PROCEDURE largetext_trigfun(id,lgtext);

--
Robert B. Easter