Thread

pg_dump of functions containing \' fail to restore (if not corrected by hand)

Robert B. Easter <reaster@comptechnews.com> — 2000-07-07T21:43:35Z

This is a minor problem, but maybe easily fixed? ...

If you create a database and load in the following sql, dump it with pg_dump,
then try to restore it (psql -e db < dump), it will get a parser error loading
the function when it encounters the "\'" in the regsub functions.  I've had many
troubles with the handling of a literal "\" in text!  They get stripped out all
the time since they are seen as escapes.  I've had to use regsub() to replace
every \ with \\ a couple times below because of the way strings do that.




-- Load the TCL procedural language
-- This could also be done with the createlang script/program.
-- See man createlang.
CREATE FUNCTION pltcl_call_handler()
	RETURNS OPAQUE AS '/usr/local/pgsql/lib/pltcl.so'
	LANGUAGE 'C';
	
CREATE TRUSTED PROCEDURAL LANGUAGE 'pltcl'
	HANDLER pltcl_call_handler
	LANCOMPILER 'PL/tcl';

-- 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 {}
	# pg_dump to psql doesnt like the quotes
	regsub -all {([\\''\\\\])} $3 {\\\\\\1} i_t
	set i_seq 0
	while { $i_t != {} } {
		set i_offset 0	
		set tblock [string range $i_t 0 [expr 7000 + $i_offset]]
		# Do not split string at a backslash
		while { [string range $tblock end end] == "\\\\" && $i_offset < 1001 } {
			set i_offset [expr $i_offset + 1]
			set tblock [string range $i_t 0 [expr 7000 + $i_offset]]
		}
		set i_t [string range $i_t [expr 7000 + [expr $i_offset + 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,
						
	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 {}
	# pg_dump to psql doesnt like the quotes
	regsub -all {([\\''\\\\])} $NEW($2) {\\\\\\1} 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_block 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