begin;

CREATE OR REPLACE FUNCTION function2(
 p_tn text
)
RETURNS SETOF record AS
$body$
DECLARE 
p_sql TEXT;
BEGIN
 p_sql := 'SELECT * FROM '||p_tn||';'; 
 RETURN QUERY EXECUTE(p_sql);
EXCEPTION 
WHEN DATA_EXCEPTION OR SYNTAX_ERROR_OR_ACCESS_RULE_VIOLATION THEN
 RAISE WARNING 'sh1';
 RAISE WARNING 'sh2';
 RAISE WARNING 'sh3';
 RAISE WARNING 'sh4';
 RAISE EXCEPTION 'TEST1';
WHEN INTEGRITY_CONSTRAINT_VIOLATION THEN
 RAISE WARNING 'sh1';
 RAISE WARNING 'sh2';
 RAISE WARNING 'sh3';
 RAISE WARNING 'sh4';
 RAISE EXCEPTION 'TEST1';
END;
$body$
LANGUAGE 'plpgsql'
STABLE
CALLED ON NULL INPUT
SECURITY DEFINER
COST 100 ROWS 1000;

create function cast_int_to_text(v bigint[]) returns text immutable language sql
as $$
  select $1::text;
$$; 

CREATE TABLE test1 (
 id bigserial NOT NULL, 
 name TEXT, 
 description TEXT, 
 document_date TIMESTAMP WITHOUT TIME ZONE DEFAULT now(), 
 oper_type SMALLINT DEFAULT 1 NOT NULL, 
 ua UUID[], 
 tst BIGINT[], 
 CONSTRAINT test_pkey PRIMARY KEY(id)
) WITHOUT OIDS;
CREATE INDEX test1_idx ON test1
 USING btree ((cast_int_to_text(tst)));
CREATE INDEX test1_idx1 ON test1
 USING gin ((to_tsvector('russian'::regconfig, name)));

insert into test1 (name, description, tst) select 'name: ' || n::text, 'desc: ' || n, array[1,2,3,n,3,2,1] from generate_series(1, 100000) as x(n);

set work_mem TO 64;

SELECT * FROM 
function2('test1')
AS T(
id BIGINT,
name TEXT,
description TEXT,
document_date TIMESTAMP,
oper_type SMALLINT,
ua UUID[],
tst BIGINT[]
);

WARNING:  temporary file leak: File 78 still referenced
КОНТЕКСТ:  PL/pgSQL function "function2" line 3 during statement block exit

WARNING:  temporary file leak: File 78 still referenced
CONTEXT:  PL/pgSQL function "function2" line 3 during statement block exit
TRAP: FailedAssertion("!(((file) > 0 && (file) < (int) SizeVfdCache && VfdCache[file].fileName != ((void *)0)))", File: "fd.c", Line: 1166)

Program received signal SIGABRT, Aborted.
0x00007fb7acee4f55 in *__GI_raise (sig=<value optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
64      ../nptl/sysdeps/unix/sysv/linux/raise.c: Нет такого файла или каталога.
        in ../nptl/sysdeps/unix/sysv/linux/raise.c
(gdb) bt
#0  0x00007fb7acee4f55 in *__GI_raise (sig=<value optimized out>) at ../nptl/sysdeps/unix/sysv/linux/raise.c:64
#1  0x00007fb7acee7d90 in *__GI_abort () at abort.c:88
#2  0x00000000006cbb5d in ExceptionalCondition (conditionName=<value optimized out>, errorType=<value optimized out>,
    fileName=<value optimized out>, lineNumber=<value optimized out>) at assert.c:57
#3  0x0000000000604e09 in FileWrite (file=0, buffer=0x16da5c8 "", amount=5728) at fd.c:1166
#4  0x0000000000605b6b in BufFileDumpBuffer (file=0x16da598) at buffile.c:300
#5  0x0000000000605dc8 in BufFileFlush (file=0x16da598, fileno=12478, offset=6, whence=-1) at buffile.c:439
#6  BufFileSeek (file=0x16da598, fileno=12478, offset=6, whence=-1) at buffile.c:510
#7  0x00000000006f5912 in tuplestore_gettuple (state=0x1700e48, forward=1 '\001', should_free=0x7fff1055ee67 "")
    at tuplestore.c:781
#8  0x00000000006f5a99 in tuplestore_gettupleslot (state=0x30be, forward=-66 '\276', copy=0 '\000', slot=0xffffffffffffffff)
    at tuplestore.c:891
#9  0x00000000005780ce in FunctionNext (node=<value optimized out>) at nodeFunctionscan.c:75
#10 0x000000000056b468 in ExecScan (node=0x30be, accessMtd=0x578090 <FunctionNext>) at execScan.c:68
#11 0x0000000000563d58 in ExecProcNode (node=0x16ba018) at execProcnode.c:385
#12 0x000000000056162a in ExecutePlan (queryDesc=0x16c1778, direction=ForwardScanDirection, count=0) at execMain.c:1504
#13 standard_ExecutorRun (queryDesc=0x16c1778, direction=ForwardScanDirection, count=0) at execMain.c:309
#14 0x000000000061dac7 in PortalRunSelect (portal=0x1697c58, forward=1 '\001', count=0, dest=0x17366b0) at pquery.c:953
#15 0x000000000061eff9 in PortalRun (portal=0x1697c58, count=9223372036854775807, isTopLevel=1 '\001', dest=0x17366b0,
    altdest=0x17366b0, completionTag=0x7fff1055f1d0 "") at pquery.c:779
#16 0x000000000061a147 in exec_simple_query (
    query_string=0x1659898 "SELECT * FROM \nfunction2('test1')\nAS T(\nid BIGINT,\nname TEXT,\ndescription TEXT,\ndocument_date TIMESTAMP,\noper_type SMALLINT,\nua UUID[],\ntst BIGINT[]\n);") at postgres.c:991
#17 0x000000000061ba83 in PostgresMain (argc=4, argv=<value optimized out>, username=0x15b89c0 "seb") at postgres.c:3614
#18 0x00000000005e8c08 in BackendRun () at postmaster.c:3449
#19 BackendStartup () at postmaster.c:3063
#20 ServerLoop () at postmaster.c:1387
#21 0x00000000005e99ad in PostmasterMain (argc=3, argv=0x15b5d50) at postmaster.c:1040
#22 0x000000000058d9d3 in main (argc=3, argv=0x15b5d50) at main.c:188

./configure --prefix=$HOME/mnt/e4/inst/pg --with-pgport=5431 --enable-nls --enable-debug --enable-cassert --enable-thread-safety --with-perl

postgres (PostgreSQL) 8.4.2
CVS (git) * REL8_4_STABLE 6f8f242 Avoid a premature coercion failure in transformSetOperationTree()
Date:   Wed Dec 16 22:24:19 2009 +0000