[BUGS] BUG #2123: join between stored procedures
The following bug has been logged online:
Bug reference: 2123
Logged by: Konstantin S. Zhinko [tIT]
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.0
Operating system: CentOS 3.5
Description:join between stored procedures
Details:
Hi all!
I have a very big problem...
Here the code:
/*SQL START*/
CREATE TABLE "public"."test_data" (
"id" INTEGER NOT NULL,
"type" INTEGER NOT NULL,
"short_text" VARCHAR(255),
CONSTRAINT "test_data_pkey" PRIMARY KEY("id")
) WITH OIDS;
CREATE TYPE "public"."ret_id" AS (
"id" INTEGER
);
CREATE TYPE "public"."test" AS (
"id" INTEGER,
"type" INTEGER,
"info" VARCHAR(255)
);
CREATE OR REPLACE FUNCTION "public"."get_obj_list" () RETURNS SETOF
"public"."ret_id" AS
$body$
declare
ret ret_id;
begin
for ret in select test_data.id from test_data
loop
return next ret;
end loop;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
CREATE OR REPLACE FUNCTION "public"."get_obj" (id integer) RETURNS
"public"."test" AS
$body$
declare
ret test;
in_id alias for $1;
begin
select * from test_data a into ret where a.id=in_id;
return ret;
end;
$body$
LANGUAGE 'plpgsql' VOLATILE CALLED ON NULL INPUT SECURITY INVOKER;
/*SQL END*/
It is ok, but when I try to join these procedures, server responses error
/*SQL START*/
SELECT b.*
FROM get_obj_list a
LEFT JOIN get_obj(a.id) b ON 1=1
/*SQL END*/
Error: relation "a" does not exists.
Why it's so?
---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster
[BUGS] BUG #2131: SQL Query Bug ?
The following bug has been logged online: Bug reference: 2131 Logged by: kenichi nakanishi Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.x Japanese Operating system: Windows 2000 Professional Japanese Description:SQL Query Bug ? Details: I found something strange result when using a following sql sentence, "select xxx || ' / ' || || ' / ' || as aaa from TABLE", sometime I could get empty results. When using same scentence on linux platform, I could get correct results. So I think it's a bug on windows version. Could you check these things ? Thank you. ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2102: Backend reports wrong number of affected rows for a
Thanks for your response. Let me see if I understand this correctly. If the original query is an update and the instead rule replaces it with an update, then I should get a return status that is not zeros. And if the original query was an update and the instead rule replaced it with something other than an update, then I should expect the return status to be zeros. In my case we have an unconditional instead rule. The original query was an update and the instead rule replaced it with an update of another table; an update was replaced with an update. Then I should expect to see the status of the last query that was inserted by the instead rule. So I should not see a status of zero. Is that correct? Thanks for all your help, Brent -Original Message-From: Tom Lane <[EMAIL PROTECTED]>To: Brent Reid <[EMAIL PROTECTED]>Cc: pgsql-bugs@postgresql.orgSent: Fri, 09 Dec 2005 11:41:26 -0500Subject: Re: [BUGS] BUG #2102: Backend reports wrong number of affected rows for a table that uses rules "Brent Reid" <[EMAIL PROTECTED]> writes: > Our Java application depends upon the return values from the various JDBC > insert, update, and delete routines. We noticed that the value is always > zero when the table that is referenced has rules associated with it. Have you read http://www.postgresql.org/docs/8.0/static/rules-status.html regards, tom lane
[BUGS] BUG #2129: dblink problem
The following bug has been logged online:
Bug reference: 2129
Logged by: Akio Iwaasa
Email address: [EMAIL PROTECTED]
PostgreSQL version: 7.4.10
Operating system: Redhat EL ES 3.0
Description:dblink problem
Details:
I'm very sorry for my poor English.
"postgres" process terminated with "signal 11"
because of my wrong SQL statement using "dblink".
--- SQL statement(Select statement a function) ---
select into RET *
from dblink(''select C1,C2,C3 from TABLE01 where ... '') < 3 column
as LINK_TABLE01(LC1 varchar(5),LC2 varchar(5),
LC3 varchar(5),LC4 varchar(5)) ;< 4 column
---
Backtrace is below.
---
(gdb) core /usr/local/pgsql74a/data/base/10218530/core.20823
Core was generated by `postgres: postgres nwops [local] CO'.
Program terminated with signal 11, Segmentation fault.
:
(gdb) bt
#0 0x00575ffb in strlen () from /lib/tls/libc.so.6
#1 0x081f222a in varcharin (fcinfo=0xbfffbf70) at
varchar.c:368
#2 0x0821b86e in FunctionCall3 (flinfo=0x89f5b60,
arg1=29795, arg2=0, arg3=64) at fmgr.c:1016
#3 0x08120647 in BuildTupleFromCStrings (attinmeta=
0x89f5b00, values=0x8a2e2f0) at execTuples.c:730
#4 0x00b5173d in dblink_record (fcinfo=0xbfffc160)
at dblink.c:699
#5 0x0811c8c8 in ExecMakeTableFunctionResult
(funcexpr=0x89f5058, econtext=0x89f4c70,
expectedDesc=0x89f4e08, returnDesc=0xbfffc244)
at execQual.c:1057
#6 0x0812882c in FunctionNext (node=0x89f4be8) at
nodeFunctionscan.c:78
#7 0x0811fba6 in ExecScan (node=0x89f4be8,
accessMtd=0x81287dc ) at
execScan.c:98
#8 0x08128906 in ExecFunctionScan (node=0x89f4be8) at
nodeFunctionscan.c:128
#9 0x0811aa0c in ExecProcNode (node=0x89f4be8) at
execProcnode.c:322
#10 0x081190b8 in ExecutePlan (estate=0x89f4ad8,
planstate=0x89f4be8, operation=CMD_SELECT,
numberTuples=1, direction=ForwardScanDirection,
dest=0x82b4734) at execMain.c:1103
#11 0x081182f5 in ExecutorRun (queryDesc=0x89e66b0,
direction=ForwardScanDirection, count=1) at
execMain.c:249
#12 0x0812fb65 in _SPI_pquery (queryDesc=0x89e66b0,
runit=1 '\001', useCurrentSnapshot=0 '\0',
tcount=1) at spi.c:1254
#13 0x0812fa5c in _SPI_execute_plan (plan=0x8a1b728,
ValuNulls=0x8a19398 " 112", useCurrentSnapshot=0
'\0', tcount=1) at spi.c:1201
#14 0x0812da75 in SPI_execp (plan=0x8a1b728, Values=
0x8a193e0, Nulls=0x8a19398 " 112", tcount=1) at
spi.c:241
#15 0x00ed97a3 in exec_run_select (estate=0xbfffc620,
expr=0x89e9170, maxtuples=1, portalP=0x0) at
pl_exec.c:3223
#16 0x00ed6abe in exec_stmt_select (estate=0xbfffc620,
stmt=0x89e9250) at pl_exec.c:1519
#17 0x00ed5eb6 in exec_stmt (estate=0xbfffc620,
stmt=0x89e9250) at pl_exec.c:967
#18 0x00ed5d3e in exec_stmts (estate=0xbfffc620,
stmts=0x89e8ed0) at pl_exec.c:903
#19 0x00ed5c28 in exec_stmt_block (estate=0xbfffc620,
block=0x89ebfd8) at pl_exec.c:859
#20 0x00ed56f0 in plpgsql_exec_trigger (func=0x89e86a8,
trigdata=0xbfffc830) at pl_exec.c:645
#21 0x00ed149f in plpgsql_call_handler
(fcinfo=0xbfffc700) at pl_handler.c:121
#22 0x0810442b in ExecCallTriggerFunc
(trigdata=0xbfffc830, finfo=0x89e26d8,
per_tuple_context=0x89e0500) at trigger.c:1150
#23 0x0810562c in DeferredTriggerExecute
(event=0x89eeb10, itemno=0, rel=0xb5549300,
trigdesc=0x89e2370, finfo=0x89e26c0,
per_tuple_context=0x89e0500) at trigger.c:1867
#24 0x0810589a in deferredTriggerInvokeEvents (
immediate_only=1 '\001') at trigger.c:2008
#25 0x08105a38 in DeferredTriggerEndQuery ()
at trigger.c:2143
#26 0x0819d61a in finish_xact_command () at
postgres.c:1757
#27 0x0819c427 in exec_simple_query
(query_string=0x89dcfb8 "COPY user_info_tbl FROM
STDIN ;") at postgres.c:946
#28 0x0819eb0e in PostgresMain (argc=4, argv=0x8990430,
username=0x89903a0 "postgres") at postgres.c:2918
#29 0x081728b8 in BackendFork (port=0x899cd70) at
postmaster.c:2564
#30 0x08171fe2 in BackendStartup (port=0x899cd70) at
postmaster.c:2207
#31 0x08170661 in ServerLoop () at postmaster.c:1119
#32 0x08170100 in PostmasterMain (argc=1, argv=0x898f538)
at postmaster.c:897
#33 0x08137ccb in main (argc=1, argv=0xbfffd9e4)
at main.c:214
(gdb) up
#1 0x081f222a in varcharin (fcinfo=0xbfffbf70)
at varchar.c:368
(gdb) p s
$10 = 0x7463
(gdb) up
#2 0x0821b86e in FunctionCall3 (flinfo=0x89f5b60,
arg1=29795, arg2=0, arg3=64) at fmgr.c:1016
(gdb) up
#3 0x08120647 in BuildTupleFromCStrings (attinmeta=
0x89f5b00, values=0x8a2e2f0) at execTuples.c:730
(gdb) p i
$12 = 3
(gdb) p values[0]
$13 = 0x8a2066c "1112"
(gdb) p values[1]
$16 = 0x8a20675 "1112"
(gdb) p values[2]
$14 = 0x8a2067e "1"
(gdb) p values[3]
$15 = 0x7463
(gdb) up
#4 0x00b5173d in dblink_record (fcinfo=0xbfffc160)
at dblink.c:699
(gdb) p *((PGresult *)funcctx->user_fctx)
$9 = {
ntups = 1,
numAttributes = 3,
attDe
[BUGS] BUG #2133: can't reinstall postgresql
The following bug has been logged online: Bug reference: 2133 Logged by: Guerra Antonio Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.1 Operating system: windows xp professional edition Description:can't reinstall postgresql Details: I installed postegresql the first time regularly. Then I deleted it using uninstall procedure because I lost the password. Today I would reinstall postgresql but my computer says: "user 'postegres' could not be created: the user account already exists.!" and stops the installation. First installation left somewhere a file with this parameter but I am not able to find it. Can you help me? Thanks in advance Guerra Antonio from Italy ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
[BUGS] BUG #2127: Regular Expression Limits Do Not Work
The following bug has been logged online:
Bug reference: 2127
Logged by: Kyril Alyoshin
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.0.3
Operating system: Windows XP
Description:Regular Expression Limits Do Not Work
Details:
I am writing a regex to verify US zipcodes. The expression is very simple,
in a trigger function it looks like this:
IF (NEW.postal_code_name !~ '\\d{5}')
THEN
RAISE EXCEPTION 'error';
END IF;
The bug description is:
If postal_code_name is > 5 digits, the expression would still evaluate to
false, and the error will not be raised.
Please correct this.
Thank you.
Kyrill
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #2118: could not connect to server
Tom Lane, I have read the email you mailed me. And I have SELinux enabled in permissive mode and the testlibpq program can run. I am very happy. In the future, I will use the PostgreSQL and give up the MySQL and other databases. I think the PostgreSQL is better than the others. Thank you very much. zhuge xiao <[EMAIL PROTECTED]> Tom Lane wrote: >"zhuge xiao" <[EMAIL PROTECTED]> writes: > > >>could not connect to server: Permission denied >>Is the server running locally and accepting connections on Unix domain >>socket "/tmp/.s.PGSQL.5432"? >> >> > >"Permission denied" indicates a file permissions problem. Check the >permissions on /tmp and on the socket file itself. If you have SELinux >enabled in enforcing mode, you might also have a SELinux policy problem >--- is your selinux-policy package up to date? > > regards, tom lane > > >
[BUGS] BUG #2126: Index usage for function value
The following bug has been logged online:
Bug reference: 2126
Logged by: Grzegorz TaÅczyk
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1
Operating system: Slackware
Description:Index usage for function value
Details:
SELECT * FROM table WHERE id = myfunction('x', 10);
There is an index created on id column, but query planner doesn't use it.
When I explain this query:
SELECT * FROM table WHERE id = (SELECT myfunction('x', 10))
Then index is used and execution is much faster.
Is this subquery nessesary?
It doesn't happen in all circumstances, but when query is more complex and
table is big then it happens.
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
[BUGS] BUG #2128: unable to install win32 version of pgsql 8.1.1
The following bug has been logged online: Bug reference: 2128 Logged by: venkat Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.1 Operating system: windows Xp service pack2 Description:unable to install win32 version of pgsql 8.1.1 Details: unable to install win32 version of pgsql 8.1.1 on my pc running on windows xp service pack2 operating system kindly help me out... ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
[BUGS] BUG #2124: Error "relation with OID ... does not exist" when using temporary table in function.
The following bug has been logged online: Bug reference: 2124 Logged by: Email address: [EMAIL PROTECTED] PostgreSQL version: 8.1.1-1 Operating system: Windows Description:Error "relation with OID ... does not exist" when using temporary table in function. Details: After running function proc_3 (described below) more than once error "relation with OID ... does not exist" occures. First run of function proc_3() is OK but the second run always fails until we recreate function proc_2(). Script to reproduce. --DROP FUNCTION proc_1(); CREATE OR REPLACE FUNCTION proc_1() RETURNS int AS $BODY$ DECLARE __nCount int; BEGIN SELECT INTO __nCount COUNT(*) FROM __tmp_xx; return __nCount; END; $BODY$ LANGUAGE 'plpgsql' VOLATILE; --DROP FUNCTION proc_2(); CREATE OR REPLACE FUNCTION proc_2() RETURNS int AS $BODY$ DECLARE __nCount int; BEGIN CREATE TEMPORARY TABLE __tmp_xx( nId int PRIMARY KEY, wstrName varchar(256) NOT NULL ); INSERT INTO __tmp_xx (nId, wstrName) VALUES (1, 'xx'); __nCount := proc_1(); DROP TABLE __tmp_xx; return __nCount; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; --DROP FUNCTION proc_3(); CREATE OR REPLACE FUNCTION proc_3() RETURNS int AS $BODY$ DECLARE __nCount int; BEGIN __nCount = proc_2(); --DELETE FROM t_res; --INSERT INTO t_res(nId, nValue) VALUES(1, __nCount); return __nCount; END $BODY$ LANGUAGE 'plpgsql' VOLATILE; SELECT * FROM proc_3(); SELECT * FROM proc_3(); ---(end of broadcast)--- TIP 6: explain analyze is your friend
[BUGS] BUG #2125: SELECT problem with strings containing \
The following bug has been logged online:
Bug reference: 2125
Logged by: Tony
Email address: [EMAIL PROTECTED]
PostgreSQL version: 80100
Operating system: XP SP2
Description:SELECT problem with strings containing \
Details:
Hi,
Please refer to the DDL and data below:
When running a query such as:
select folder from public.folders
where lower(folder) like '%c:\\1%'
no rows are returned
However,
select folder from public.folders
where lower(folder) like '%c:%'
does work OK.
When the query string contains a \\ (escaped \) no results are returned.
Other combinations not containing this character work OK. I've tried without
the % wildcard without success so this does not appear to be the problem.
I've also tried it without the lower function either, still no luck. Using
the \\ to insert records works fine (as shown below).
Table DDL:
--
create table public.folders(
folder varchar(255) ,
lastfolder varchar(255) ) with oids
Here are some INSERTS to populate data:
---
insert into public.folders values ('C:\\1\\', '1');
insert into public.folders values ('C:\\111\\', '111');
insert into public.folders values ('C:\\115x\\', '115x');
insert into public.folders values ('C:\\11tx\\', '11tx');
insert into public.folders values ('C:\\2005\\', '2005');
insert into public.folders values ('C:\\2005\\(12)December\\',
'(12)December');
insert into public.folders values ('C:\\2005\\(12)December\\051209\\',
'051209');
insert into public.folders values ('C:\\2005\\12 December\\', '12
December');
insert into public.folders values ('C:\\2005\\12 December\\12\\', '12');
insert into public.folders values ('C:\\776\\', '776');
---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings
[BUGS] BUG #2122: Inconsistent FK Error Messages
The following bug has been logged online:
Bug reference: 2122
Logged by: David Wheeler
Email address: [EMAIL PROTECTED]
PostgreSQL version: 8.1.0
Operating system: Mac OS X 10.4.3
Description:Inconsistent FK Error Messages
Details:
I noticed that when I update or insert a record that violates a foreign key
constraint, I get an error such as 'insert or update on table "b" violates
foreign key constraint "b_a_id_fkey"', but when I delete a record where the
primary key is referenced as a foreign key elsewhere, and the FK constraint
is RESTRICT, I get an erro such as 'update or delete on "a" violates foreign
key constraint "b_a_id_fkey" on "b"'. The bit that's inconsistent between
them is that the former says 'table "b"' while the latter just says
'"a"'--no 'table'.
Here's how to reproduce it:
sharky=# create table a (id serial primary key);
NOTICE: CREATE TABLE will create implicit sequence "a_id_seq" for serial
column "a.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "a_pkey" for
table "a"
CREATE TABLE
sharky=# create table b(id serial primary key, a_id int references a(id));
NOTICE: CREATE TABLE will create implicit sequence "b_id_seq" for serial
column "b.id"
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "b_pkey" for
table "b"
CREATE TABLE
sharky=# insert into a (id) values (nextval('a_id_seq'));
INSERT 0 1
sharky=# insert into b (a_id) values(currval('a_id_seq'));
INSERT 0 1
sharky=# insert into b (a_id) values(-1);
ERROR: insert or update on table "b" violates foreign key constraint
"b_a_id_fkey"
DETAIL: Key (a_id)=(-1) is not present in table "a".
sharky=# delete from a;
ERROR: update or delete on "a" violates foreign key constraint
"b_a_id_fkey" on "b"
DETAIL: Key (id)=(1) is still referenced from table "b".
sharky=#
No biggie, but I though it was worth knowing.
Thanks!
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [BUGS] BUG #2118: could not connect to server [From zhuge xiao <[EMAIL PROTECTED]>]
Tom Lane, I have read the email you mailed me yesterday. And I have SELinux enabled in permissive mode and the testlibpq program can run. I am very happy. In the future, I will use the PostgreSQL and give up the MySQL and other databases. I think the PostgreSQL is better than the others. Thank you very much. zhuge xiao <[EMAIL PROTECTED]> Tom Lane wrote: >>"zhuge xiao" <[EMAIL PROTECTED]> writes: >> >> > could not connect to server: Permission denied Is the server running locally and accepting connections on Unix domain socket "/tmp/.s.PGSQL.5432"? > >> >>"Permission denied" indicates a file permissions problem. Check the >>permissions on /tmp and on the socket file itself. If you have SELinux >>enabled in enforcing mode, you might also have a SELinux policy problem >>--- is your selinux-policy package up to date? >> >> regards, tom lane >> >> >> __ Yahoo! DSL Something to write home about. Just $16.99/mo. or less. dsl.yahoo.com ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2123: join between stored procedures
"Konstantin S. Zhinko [tIT]" <[EMAIL PROTECTED]> writes: > SELECT b.* > FROM get_obj_list a > LEFT JOIN get_obj(a.id) b ON 1=1 > Error: relation "a" does not exists. This is not a bug. You cannot make use of values from one inside the definition of another . So the reference "a.id" is taken to be to a real table named "a", not to the "a" alias elsewhere in the query. You could do something like SELECT (get_obj(id)).* FROM get_obj_list(); instead. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2102: Backend reports wrong number of affected rows for a
[EMAIL PROTECTED] writes: > In my case we have an unconditional instead rule. The original query was an > update and the instead rule replaced it with an update of another table; an > update was replaced with an update. Then I should expect to see the status > of the last query that was inserted by the instead rule. So I should not see > a status of zero. Well, you'll see the status of the last UPDATE executed due to a rule ... but that doesn't mean it couldn't have updated zero rows. It might be worth pointing out here that conditional rules insert queries that have the condition added to their WHERE clause; if the condition is false then no rows are going to get processed. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2127: Regular Expression Limits Do Not Work
"Kyril Alyoshin" <[EMAIL PROTECTED]> writes:
> IF (NEW.postal_code_name !~ '\\d{5}')
> The bug description is:
> If postal_code_name is > 5 digits, the expression would still evaluate to
> false, and the error will not be raised.
> Please correct this.
Please learn how to use regular expressions ;-)
You probably want '^\\d{5}$' instead, to require the regex to match the
whole string instead of just any part of it.
regards, tom lane
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2126: Index usage for function value
"Grzegorz TaÅczyk" <[EMAIL PROTECTED]> writes:
> SELECT * FROM table WHERE id = myfunction('x', 10);
> There is an index created on id column, but query planner doesn't use it.
Have you declared myfunction as IMMUTABLE or STABLE? If it's volatile
then optimizing to an indexscan is incorrect and the planner won't do it.
regards, tom lane
---(end of broadcast)---
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
Re: [BUGS] BUG #2125: SELECT problem with strings containing \
"Tony" <[EMAIL PROTECTED]> writes: > When running a query such as: > select folder from public.folders > where lower(folder) like '%c:\\1%' > no rows are returned This is not a bug --- you've forgotten that backslash is an escape character in LIKE patterns. You can either double it again: like '%c:1%' or select a different escape character, or maybe better not have any escape character at all: like '%c:\\1%' escape '' See http://www.postgresql.org/docs/8.1/static/functions-matching.html#FUNCTIONS-LIKE regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [BUGS] BUG #2124: Error "relation with OID ... does not exist" when
See our FAQ about temporary tables: 4.19) Why do I get "relation with OID # does not exist" errors when accessing temporary tables in PL/PgSQL functions? --- [EMAIL PROTECTED] wrote: > > The following bug has been logged online: > > Bug reference: 2124 > Logged by: > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.1-1 > Operating system: Windows > Description:Error "relation with OID ... does not exist" when using > temporary table in function. > Details: > > After running function proc_3 (described below) more than once error > "relation with OID ... does not exist" occures. > > First run of function proc_3() is OK but the second run always fails until > we recreate function proc_2(). > > Script to reproduce. > > > > --DROP FUNCTION proc_1(); > > CREATE OR REPLACE FUNCTION proc_1() RETURNS int AS > $BODY$ > DECLARE > __nCount int; > BEGIN > SELECT INTO __nCount COUNT(*) FROM __tmp_xx; > return __nCount; > END; > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > --DROP FUNCTION proc_2(); > > CREATE OR REPLACE FUNCTION proc_2() RETURNS int AS > $BODY$ > DECLARE > __nCount int; > BEGIN > CREATE TEMPORARY TABLE __tmp_xx( > nId int PRIMARY KEY, > wstrName varchar(256) NOT NULL > ); > INSERT INTO __tmp_xx (nId, wstrName) VALUES (1, 'xx'); > __nCount := proc_1(); > DROP TABLE __tmp_xx; > return __nCount; > END > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > --DROP FUNCTION proc_3(); > > CREATE OR REPLACE FUNCTION proc_3() RETURNS int AS > $BODY$ > DECLARE > __nCount int; > BEGIN > __nCount = proc_2(); > --DELETE FROM t_res; > --INSERT INTO t_res(nId, nValue) VALUES(1, __nCount); > return __nCount; > END > $BODY$ > LANGUAGE 'plpgsql' VOLATILE; > > > SELECT * FROM proc_3(); > > SELECT * FROM proc_3(); > > > > ---(end of broadcast)--- > TIP 6: explain analyze is your friend > -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2131: SQL Query Bug ?
On Mon, Dec 26, 2005 at 03:47:36PM +, kenichi nakanishi wrote:
> I found something strange result when using a following sql sentence,
> "select xxx || ' / ' || || ' / ' || as aaa from TABLE",
> sometime I could get empty results.
> When using same scentence on linux platform, I could get correct results.
> So I think it's a bug on windows version.
Do the Linux and Windows platforms have the same data? Might any
of the columns on the Windows system be NULL? Concatenating anything
with NULL results in NULL, so that could be the problem.
test=> CREATE TABLE foo (col1 text, col2 text);
CREATE TABLE
test=> INSERT INTO foo (col1, col2) VALUES ('aaa', 'bbb');
INSERT 0 1
test=> INSERT INTO foo (col1, col2) VALUES ('ccc', NULL);
INSERT 0 1
test=> INSERT INTO foo (col1, col2) VALUES (NULL, 'ddd');
INSERT 0 1
test=> SELECT col1, col2, col1 || col2 FROM foo;
col1 | col2 | ?column?
--+--+--
aaa | bbb | aaabbb
ccc | |
| ddd |
(3 rows)
If you want to treat NULL as an empty string then use COALESCE:
test=> SELECT col1, col2, COALESCE(col1, '') || COALESCE(col2, '') FROM foo;
col1 | col2 | ?column?
--+--+--
aaa | bbb | aaabbb
ccc | | ccc
| ddd | ddd
(3 rows)
--
Michael Fuhr
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2128: unable to install win32 version of pgsql 8.1.1
venkat wrote: > > The following bug has been logged online: > > Bug reference: 2128 > Logged by: venkat > Email address: [EMAIL PROTECTED] > PostgreSQL version: 8.1.1 > Operating system: windows Xp service pack2 > Description:unable to install win32 version of pgsql 8.1.1 > Details: > > unable to install win32 version of pgsql 8.1.1 on my pc running on windows > xp service pack2 operating system > > > kindly help me out... Without more information about the failure, we can't help you. -- Bruce Momjian| http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 359-1001 + If your life is a hard drive, | 13 Roberts Road + Christ can be your backup.| Newtown Square, Pennsylvania 19073 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [BUGS] BUG #2129: dblink problem
We have significantly improved dblink since 7.4. Would you try 8.1 and
see if you can reproduce the problem?
---
Akio Iwaasa wrote:
>
> The following bug has been logged online:
>
> Bug reference: 2129
> Logged by: Akio Iwaasa
> Email address: [EMAIL PROTECTED]
> PostgreSQL version: 7.4.10
> Operating system: Redhat EL ES 3.0
> Description:dblink problem
> Details:
>
> I'm very sorry for my poor English.
>
> "postgres" process terminated with "signal 11"
> because of my wrong SQL statement using "dblink".
>
> --- SQL statement(Select statement a function) ---
> select into RET *
> from dblink(''select C1,C2,C3 from TABLE01 where ... '') < 3 column
>as LINK_TABLE01(LC1 varchar(5),LC2 varchar(5),
>LC3 varchar(5),LC4 varchar(5)) ;< 4 column
> ---
>
> Backtrace is below.
>
> ---
> (gdb) core /usr/local/pgsql74a/data/base/10218530/core.20823
> Core was generated by `postgres: postgres nwops [local] CO'.
> Program terminated with signal 11, Segmentation fault.
> :
> (gdb) bt
> #0 0x00575ffb in strlen () from /lib/tls/libc.so.6
> #1 0x081f222a in varcharin (fcinfo=0xbfffbf70) at
> varchar.c:368
> #2 0x0821b86e in FunctionCall3 (flinfo=0x89f5b60,
> arg1=29795, arg2=0, arg3=64) at fmgr.c:1016
> #3 0x08120647 in BuildTupleFromCStrings (attinmeta=
> 0x89f5b00, values=0x8a2e2f0) at execTuples.c:730
> #4 0x00b5173d in dblink_record (fcinfo=0xbfffc160)
> at dblink.c:699
> #5 0x0811c8c8 in ExecMakeTableFunctionResult
> (funcexpr=0x89f5058, econtext=0x89f4c70,
> expectedDesc=0x89f4e08, returnDesc=0xbfffc244)
> at execQual.c:1057
> #6 0x0812882c in FunctionNext (node=0x89f4be8) at
> nodeFunctionscan.c:78
> #7 0x0811fba6 in ExecScan (node=0x89f4be8,
> accessMtd=0x81287dc ) at
> execScan.c:98
> #8 0x08128906 in ExecFunctionScan (node=0x89f4be8) at
> nodeFunctionscan.c:128
> #9 0x0811aa0c in ExecProcNode (node=0x89f4be8) at
> execProcnode.c:322
> #10 0x081190b8 in ExecutePlan (estate=0x89f4ad8,
> planstate=0x89f4be8, operation=CMD_SELECT,
> numberTuples=1, direction=ForwardScanDirection,
> dest=0x82b4734) at execMain.c:1103
> #11 0x081182f5 in ExecutorRun (queryDesc=0x89e66b0,
> direction=ForwardScanDirection, count=1) at
> execMain.c:249
> #12 0x0812fb65 in _SPI_pquery (queryDesc=0x89e66b0,
> runit=1 '\001', useCurrentSnapshot=0 '\0',
> tcount=1) at spi.c:1254
> #13 0x0812fa5c in _SPI_execute_plan (plan=0x8a1b728,
> ValuNulls=0x8a19398 " 112", useCurrentSnapshot=0
> '\0', tcount=1) at spi.c:1201
> #14 0x0812da75 in SPI_execp (plan=0x8a1b728, Values=
> 0x8a193e0, Nulls=0x8a19398 " 112", tcount=1) at
> spi.c:241
> #15 0x00ed97a3 in exec_run_select (estate=0xbfffc620,
> expr=0x89e9170, maxtuples=1, portalP=0x0) at
> pl_exec.c:3223
> #16 0x00ed6abe in exec_stmt_select (estate=0xbfffc620,
> stmt=0x89e9250) at pl_exec.c:1519
> #17 0x00ed5eb6 in exec_stmt (estate=0xbfffc620,
> stmt=0x89e9250) at pl_exec.c:967
> #18 0x00ed5d3e in exec_stmts (estate=0xbfffc620,
> stmts=0x89e8ed0) at pl_exec.c:903
> #19 0x00ed5c28 in exec_stmt_block (estate=0xbfffc620,
> block=0x89ebfd8) at pl_exec.c:859
> #20 0x00ed56f0 in plpgsql_exec_trigger (func=0x89e86a8,
> trigdata=0xbfffc830) at pl_exec.c:645
> #21 0x00ed149f in plpgsql_call_handler
> (fcinfo=0xbfffc700) at pl_handler.c:121
> #22 0x0810442b in ExecCallTriggerFunc
> (trigdata=0xbfffc830, finfo=0x89e26d8,
> per_tuple_context=0x89e0500) at trigger.c:1150
> #23 0x0810562c in DeferredTriggerExecute
> (event=0x89eeb10, itemno=0, rel=0xb5549300,
> trigdesc=0x89e2370, finfo=0x89e26c0,
> per_tuple_context=0x89e0500) at trigger.c:1867
> #24 0x0810589a in deferredTriggerInvokeEvents (
> immediate_only=1 '\001') at trigger.c:2008
> #25 0x08105a38 in DeferredTriggerEndQuery ()
> at trigger.c:2143
> #26 0x0819d61a in finish_xact_command () at
> postgres.c:1757
> #27 0x0819c427 in exec_simple_query
> (query_string=0x89dcfb8 "COPY user_info_tbl FROM
> STDIN ;") at postgres.c:946
> #28 0x0819eb0e in PostgresMain (argc=4, argv=0x8990430,
> username=0x89903a0 "postgres") at postgres.c:2918
> #29 0x081728b8 in BackendFork (port=0x899cd70) at
> postmaster.c:2564
> #30 0x08171fe2 in BackendStartup (port=0x899cd70) at
> postmaster.c:2207
> #31 0x08170661 in ServerLoop () at postmaster.c:1119
> #32 0x08170100 in PostmasterMain (argc=1, argv=0x898f538)
> at postmaster.c:897
> #33 0x08137ccb in main (argc=1, argv=0xbfffd9e4)
> at main.c:214
> (gdb) up
> #1 0x081f222a in varcharin (fcinfo=0xbfffbf70)
> at varchar.c:368
> (gdb) p s
> $10 = 0x7463
> (gdb) up
> #2 0x0821b86e in FunctionCall3 (flinfo=0x89f5b60,
> arg1=29795, arg2=0, arg3=64) at fmgr.c:1016
> (gdb) up
> #3 0x
