Re: [SQL] Mystery function error
Josh Berkus <[EMAIL PROTECTED]> writes: > Also, this will run faster if you do it as a SQL function: > CREATE FUNCTION locate ( text, text ) RETURNS INT AS ' > SELECT POSITION($2, $1); > ' LANGUAGE SQL IMMUTABLE STRICT; This is definitely the solution I'd recommend for 7.4 (because 7.4 would inline the SQL function definition, resulting in zero runtime overhead). In 7.3 I suspect the plpgsql version might be a tad faster, or anyway comparable. Has anyone done any head-to-head performance comparisons on such simple functions? regards, tom lane ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Mystery function error
Tom Lane wrote:
Josh Berkus <[EMAIL PROTECTED]> writes:
Also, this will run faster if you do it as a SQL function:
CREATE FUNCTION locate ( text, text ) RETURNS INT AS '
SELECT POSITION($2, $1);
' LANGUAGE SQL IMMUTABLE STRICT;
This is definitely the solution I'd recommend for 7.4 (because 7.4 would
inline the SQL function definition, resulting in zero runtime overhead).
In 7.3 I suspect the plpgsql version might be a tad faster, or anyway
comparable. Has anyone done any head-to-head performance comparisons
on such simple functions?
I did a quick check last night on 7.3.4 and found that plpgsql was faster:
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
-- search for the position of $2 in $1
declare
srcstr alias for $1;
searchstr alias for $2;
begin
return position(searchstr in srcstr);
end;
' LANGUAGE 'plpgsql' IMMUTABLE;
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)
DROP FUNCTION public.locate(bpchar, bpchar);
CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS '
select position($2 in $1)
' LANGUAGE 'sql';
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.24..0.24
rows=1 loops=1)
Total runtime: 0.26 msec
(2 rows)
On 7.4 (different hardware), I get this:
plpgsql
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.00..0.00
rows=1 loops=1)
Total runtime: 0.05 msec
(2 rows)
sql
regression=# explain analyze select locate('abc','b');
QUERY PLAN
--
Result (cost=0.00..0.01 rows=1 width=0) (actual time=0.01..0.01
rows=1 loops=1)
Total runtime: 0.03 msec
(2 rows)
Joe
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Mystery function error
Tom, > position()1 usec/call 1 usec/call > SQL func 1 usec/call 90 usec/call > plpgsql func 110 usec/call 100 usec/call Hmmm ... this does still seem to show that plpgsql is 10% slower in 7.4. Any idea why? -- Josh Berkus Aglio Database Solutions San Francisco ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
Re: [SQL] Mystery function error
Josh Berkus <[EMAIL PROTECTED]> writes: > Hmmm ... this does still seem to show that plpgsql is 10% slower in > 7.4. Yeah, I just did some profiling to check on that, and it seems there is extra overhead in plpgsql's exec_eval_simple_expr(), to the tune of another palloc or two down inside CreateExprContext(). This probably would not show up so heavily in a more complex plpgsql function, but it's a noticeable percentage in this trivial example. I had noticed this before but I'm not sure there's any easy solution. Awhile back I looked at saving the exprcontext across calls instead of creating and deleting it on each call to exec_eval_simple_expr, but the idea broke down in situations involving recursive plpgsql functions. I'll take another look though. regards, tom lane ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
Dan Langille <[EMAIL PROTECTED]> writes: > WHERE lastlogin between current_date - interval \''' || > quote_literal(i - 1) || '' days\' > AND current_date - interval \''' || > quote_literal(i) || '' days\'''; IIRC, quote_literal() puts single quotes around its result. So you have too many quotes there. Given that you know i is an integer, you don't really need quote_literal for it. Actually, you don't need EXECUTE here at all. Why not just FOR i IN 1..MaxDays LOOP SELECT count(*) INTO r FROM users WHERE lastlogin between current_date - (i-1) * interval ''1 day'' AND current_date - i * interval ''1 day''; RETURN NEXT r; END LOOP; regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] SUM() & GROUP BY
On Sun, 2003-09-28 at 19:01, Muhyiddin A.M Hayat wrote:
>
> hotel=# SELECT
> hotel-# "public".billing.id,
> hotel-# "public".billing.guest_id,
> hotel-# "public".billing.trx_date,
> hotel-# "public".billing.trx_time,
> hotel-# "public".billing.payment_method,
> hotel-# "public".billing.tax,
> hotel-# "public".billing.dep_id,
> hotel-# "public".department."name",
> hotel-# SUM("public".items.price) AS total,
> hotel-# "public".billing.amount_paid
> hotel-# FROM
> hotel-# "public".billing_items
> hotel-# INNER JOIN "public".billing ON
> ("public".billing_items.billing_id = "public".billing.id)
> hotel-# INNER JOIN "public".department ON ("public".billing.dep_id =
> "public".department.id)
> hotel-# INNER JOIN "public".items ON
> ("public".billing_items.items_id = "public".items.id)
> hotel-# GROUP BY "public".billing.id;
> ERROR: Attribute billing.guest_id must be GROUPed or used in an
> aggregate function
> hotel=#
>
> What Worng ??
Any items in the select list need to be aggregated (e.g.
SUM("public".items.price)) or mentioned in the GROUP BY list. Suppose
there are several billing.guest_id values for each billing.id; which
value should be listed in the output?
--
Oliver Elphick[EMAIL PROTECTED]
Isle of Wight, UK http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
"Blessed is the man that endureth temptation; for when
he is tried, he shall receive the crown of life, which
the Lord hath promised to them that love him."
James 1:12
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL]
On 28 Sep 2003 at 15:45, Tom Lane wrote: > Dan Langille <[EMAIL PROTECTED]> writes: > > WHERE lastlogin between current_date - interval \''' || > > quote_literal(i - 1) || '' days\' > > AND current_date - interval \''' || > > quote_literal(i) || '' days\'''; > > IIRC, quote_literal() puts single quotes around its result. So you have > too many quotes there. Given that you know i is an integer, you don't > really need quote_literal for it. Actually, you don't need EXECUTE > here at all. Why not just > > FOR i IN 1..MaxDays LOOP > SELECT count(*) > INTO r > FROM users > WHERE lastlogin between current_date - (i-1) * interval ''1 day'' > AND current_date - i * interval ''1 day''; > RETURN NEXT r; > END LOOP; Thank you. I had to replace the " with \', but here is what I came up with (after adding another item to the SELECT): CREATE OR REPLACE FUNCTION LoginCounts(int) RETURNS SETOF logincounts_record AS ' DECLARE MaxDays ALIAS for $1; r logincounts_record%rowtype; i integer; BEGIN raise notice ''MaxDays''; FOR i IN 1..MaxDays LOOP SELECT 1 AS days, count(*) as count INTO r FROM users WHERE lastlogin between current_date - (i-1) * interval \'1 day\' AND current_date - i * interval \'1 day\'; RETURN NEXT r; END LOOP; RETURN; END ' LANGUAGE plpgsql; However, the results are confusing. I'm getting the wrong number of parameters. The value being returned appears to be the value supplied. But the log results show an interesting pattern in the number of selects being run. working-copy.freshports.org=# select count(*) from LoginCounts(1); NOTICE: MaxDays count --- 1 (1 row) The log says: 2003-09-28 16:01:54 [32813] LOG: query: select count(*) from LoginCounts(1); 2003-09-28 16:01:54 [32813] NOTICE: MaxDays 2003-09-28 16:01:54 [32813] LOG: query: select cast($1 as timestamp without time zone) - $2; working-copy.freshports.org=# select count(*) from LoginCounts(2); NOTICE: MaxDays count --- 2 (1 row) And the log says: 2003-09-28 16:02:04 [32813] LOG: query: select count(*) from LoginCounts(2); 2003-09-28 16:02:04 [32813] NOTICE: MaxDays 2003-09-28 16:02:04 [32813] LOG: query: select cast($1 as timestamp without time zone) - $2; 2003-09-28 16:02:04 [32813] LOG: query: select cast($1 as timestamp without time zone) - $2; 2003-09-28 16:02:04 [32813] LOG: query: select cast($1 as timestamp without time zone) - $2; The type in question is: CREATE TYPE logincounts_record AS ( daysinteger, count integer ); -- Dan Langille : http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL]
"Dan Langille" <[EMAIL PROTECTED]> writes: > However, the results are confusing. I'm getting the wrong number of > parameters. The value being returned appears to be the value > supplied. But the log results show an interesting pattern in the > number of selects being run. I dunno where the cast() queries are coming from, but note that they're not your SELECT. You are misunderstanding how the code works if you expect to see query LOG entries from plpgsql queries. For a non-EXECUTEd plpgsql command, log_statement will only show the query the first time it is executed in a session, because that log entry is generated as a side-effect of parsing and planning. As a means of tracing the execution of plpgsql functions, log_statement is pretty worthless :-(. I would like us to develop a full-up tracing and debugging facility for plpgsql, but we haven't got it yet. regards, tom lane ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [SQL] Mystery function error
I said: > [hmm, I wonder why plpgsql seems to have gotten slower in 7.4...] False alarm --- or at least, it wasn't plpgsql's fault. I copied Joe's function definition, which was CREATE OR REPLACE FUNCTION public.locate(bpchar, bpchar) RETURNS int4 AS ' But since position() takes arguments of type TEXT, there are two bpchar->text coercions involved inside the function. This was a "free" binary coercion in 7.3, but 7.4 interprets it as invoking rtrim(). The extra rtrim operations account for most of the slowdown. Using functions declared like "locate(text, text)" to avoid the unwanted type coercions, I get these kinds of numbers: 7.4 7.3 position() 1 usec/call 1 usec/call SQL func1 usec/call 90 usec/call plpgsql func110 usec/call 100 usec/call regards, tom lane ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faqs/FAQ.html
[SQL]
I'm trying to create a function which returns a result set using a dynamic query. The problem occurs when it compiles. I suspect it's my quoting, but I'm not sure of the cause. CREATE OR REPLACE FUNCTION LoginCounts(int) RETURNS SETOF logincounts_record AS ' DECLARE MaxDays ALIAS for $1; r logincounts_record%rowtype; i integer; BEGIN FOR i IN 1..MaxDays LOOP EXECUTE '' SELECT count(*) INTO r FROM users WHERE lastlogin between current_date - interval \''' || quote_literal(i - 1) || '' days\' AND current_date - interval \''' || quote_literal(i) || '' days\'''; RETURN NEXT r; END LOOP; RETURN; END ' LANGUAGE plpgsql; # select * from LoginCounts(2); WARNING: Error occurred while executing PL/pgSQL function logincounts WARNING: line 9 at execute statement ERROR: parser: parse error at or near "days" at character 151 thnks -- Dan Langille - http://www.langille.org/ ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] Mystery function error
Joe Conway <[EMAIL PROTECTED]> writes:
>> ... Has anyone done any head-to-head performance comparisons
>> on such simple functions?
> I did a quick check last night on 7.3.4 and found that plpgsql was faster:
> regression=# explain analyze select locate('abc','b');
Er ... I'm not sure you're measuring anything reliable there. In
particular, since you declared the plpgsql function immutable, the
planner would have reduced this function call to a constant on sight,
and there is no evaluation happening at runtime at all. The SQL
version shows as faster only because you neglected to mark it as
immutable; else it'd have gotten the same treatment. In any case, a
single call of a simple function is likely to be swamped by executor
startup/shutdown overhead.
I tried the same function definitions using a test like this
explain analyze select locate(f1,'b') from t1;
where t1 is a 1-row table with a single text column. The results I
get are that the SQL function is very marginally faster than the plpgsql
one in 7.3 (1200 vs 1350 msec), and significantly faster in 7.4
(385 vs 1600 msec). The basic table-scan overhead can be determined
from
explain analyze select f1,'b' from t1;
which shows as about 330 msec in both versions; subtracting that off
tells you how long it actually took to do 1 function calls.
[hmm, I wonder why plpgsql seems to have gotten slower in 7.4...]
regards, tom lane
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
[SQL] SUM() & GROUP BY
hotel=# SELECThotel-#
"public".billing.id,hotel-#
"public".billing.guest_id,hotel-#
"public".billing.trx_date,hotel-#
"public".billing.trx_time,hotel-#
"public".billing.payment_method,hotel-#
"public".billing.tax,hotel-#
"public".billing.dep_id,hotel-#
"public".department."name",hotel-# SUM("public".items.price) AS
total,hotel-# "public".billing.amount_paidhotel-#
FROMhotel-# "public".billing_itemshotel-# INNER
JOIN "public".billing ON ("public".billing_items.billing_id =
"public".billing.id)hotel-# INNER JOIN "public".department ON
("public".billing.dep_id = "public".department.id)hotel-# INNER
JOIN "public".items ON ("public".billing_items.items_id =
"public".items.id)hotel-# GROUP BY
"public".billing.id;ERROR: Attribute billing.guest_id must be GROUPed
or used in an aggregate functionhotel=#
What Worng ??
How to using SUM() & GROUP
BY
Re: [SQL] SUM() & GROUP BY
so
SELECT
"public".billing.id,
"public".billing.guest_id,
"public".billing.trx_date,
"public".billing.trx_time,
"public".department."name" AS depart,
"public".payment_method.description AS payment_method,
"public".billing.amount_paid,
"public".billing.tax,
"public".billing.creator
FROM
"public".payment_method
INNER JOIN "public".billing ON ("public".payment_method.id =
"public".billing.payment_method)
INNER JOIN "public".department ON ("public".billing.dep_id =
"public".department.id)
INNER JOIN "public".billing_items ON ("public".billing.id =
"public".billing_items.billing_id)
Result:
id | guest_id | trx_date | trx_time | depart | payment_method
|amount_paid | tax | creator
+--++--+++--
---+-+-
1 |1 | 2003-09-28 | 16:08:52 | Resto | Cash |2.00 |
10 | middink
1 |1 | 2003-09-28 | 16:08:52 | Resto | Cash |2.00 |
10 | middink
2 |1 | 2003-09-29 | 07:50:17 | Resto | Visa |1.00 |
10 | middink
2 |1 | 2003-09-29 | 07:50:17 | Resto | Visa |1.00 |
10 | middink
So, i would like to view billing amount, value billing amount
sum(item.price) from , so my new query :
SELECT
"public".billing.id,
"public".billing.guest_id,
"public".billing.trx_date,
"public".billing.trx_time,
"public".department."name" AS depart,
"public".payment_method.description AS payment_method,
"public".billing.tax,
(SUM(("public".items.price *
"public".billing_items.quantity)-("public".billing_items.discount))
*
("public".billing.tax/100)) AS tax_amount,
(SUM(("public".items.price * "public".billing_items.quantity)))
AS billing_amount,
(SUM(("public".items.price * "public".billing_items.quantity))
-
(SUM(("public".items.price *
"public".billing_items.quantity))*("public".billing.tax/100))
)
AS total,
"public".billing.amount_paid,
"public".billing.creator
FROM
"public".payment_method
INNER JOIN "public".billing ON ("public".payment_method.id =
"public".billing.payment_method)
INNER JOIN "public".department ON ("public".billing.dep_id =
"public".department.id)
INNER JOIN "public".billing_items ON ("public".billing.id =
"public".billing_items.billing_id)
INNER JOIN "public".items ON ("public".billing_items.billing_id =
"public".items.id)
GROUP BY
"public".billing.id,
"public".billing.guest_id,
"public".billing.trx_date,
"public".billing.trx_time,
"public".department."name",
"public".payment_method.description,
"public".billing.amount_paid,
"public".billing.tax,
"public".billing.creator
Result:
id | guest_id | trx_date | trx_time | depart | payment_method | tax
|tax_amount | billing_amount | total | amount_paid | creator
+--++--+++-+
++---+-+-
1 |1 | 2003-09-28 | 16:08:52 | Resto | Cash | 10 |3600
| 36000. | 32400 |2.00 | middink
2 |1 | 2003-09-29 | 07:50:17 | Resto | Visa | 10 |1200
| 12000. | 10800 |1.00 | middink
but i have another problem :
- how to simple below statment :
(SUM(("public".items.price *
"public".billing_items.quantity)-("public".billing_items.discount))
*
("public".billing.tax/100)) AS tax_amount,
(SUM(("public".items.price * "public".billing_items.quantity)))
AS billing_amount,
(SUM(("public".items.price * "public".billing_items.quantity))
-
(SUM(("public".items.price *
"public".billing_items.quantity))*("public".billing.tax/100))
)
AS total,
- I have discount in public".billing_items.discount, how to including the
discount to billing_amount
- Original Message -
From: "Oliver Elphick" <[EMAIL PROTECTED]>
To: "Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Monday, September 29, 2003 3:56 AM
Subject: Re: [SQL] SUM() & GROUP BY
> Any items in the select list need to be aggregated (e.g.
> SUM("public".items.price)) or mentioned in the GROUP BY list. Suppose
> there are several billing.guest_id values for each billing.id; which
> value should be listed in the output?
>
>
> --
> Oliver Elphick[EMAIL PROTECTED]
> Isle of Wight, UK http://www.lfix.co.uk/oliver
> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C
>
> "Blessed is the man that endureth temptation; for when
> he is tried, he shall receive the crown of life, which
> the Lord hath promised to them that love him."
> James 1:12
>
billing.sql
Description: Binary data
---(end of broadcast)---
TIP 8: explain analyze is your friend
Re: [SQL] SUM() & GROUP BY
SELECT "public".billing.id,
"public".billing.guest_id, "public".billing.trx_date,
"public".billing.trx_time, "public".department."name" AS
depart, "public".payment_method.description AS
payment_method, "public".billing.amount_paid,
"public".billing.tax, "public".billing.creatorFROM
"public".payment_method INNER JOIN "public".billing ON
("public".payment_method.id ="public".billing.payment_method)
INNER JOIN "public".department ON ("public".billing.dep_id
="public".department.id) INNER JOIN "public".billing_items ON
("public".billing.id
="public".billing_items.billing_id)Result: id |
guest_id | trx_date | trx_time | depart |
payment_method|amount_paid | tax |
creator+--++--+++-+-+-
1 | 1 | 2003-09-28 | 16:08:52 |
Resto | Cash
|2.00 |10 | middink 1
| 1 | 2003-09-28 | 16:08:52 |
Resto | Cash
|2.00 |10 | middink 2
| 1 | 2003-09-29 | 07:50:17 |
Resto | Visa
|1.00 |10 | middink 2
| 1 | 2003-09-29 | 07:50:17 |
Resto | Visa
|1.00 |10 | middinkSo, i would like to view billing amount,
value billing amountsum(item.price) from , so my new query
:SELECT "public".billing.id,
"public".billing.guest_id, "public".billing.trx_date,
"public".billing.trx_time, "public".department."name" AS
depart, "public".payment_method.description AS
payment_method, "public".billing.tax,
(SUM(("public".items.price
*"public".billing_items.quantity)-("public".billing_items.discount))
* ("public".billing.tax/100)) AS tax_amount,
(SUM(("public".items.price * "public".billing_items.quantity))) AS
billing_amount, (SUM(("public".items.price *
"public".billing_items.quantity)) -
(SUM(("public".items.price
*"public".billing_items.quantity))*("public".billing.tax/100))
) AS total, "public".billing.amount_paid,
"public".billing.creatorFROM "public".payment_method
INNER JOIN "public".billing ON ("public".payment_method.id
="public".billing.payment_method) INNER JOIN "public".department
ON ("public".billing.dep_id ="public".department.id) INNER JOIN
"public".billing_items ON ("public".billing.id
="public".billing_items.billing_id) INNER JOIN "public".items ON
("public".billing_items.billing_id ="public".items.id)GROUP
BY "public".billing.id, "public".billing.guest_id,
"public".billing.trx_date, "public".billing.trx_time,
"public".department."name",
"public".payment_method.description,
"public".billing.amount_paid, "public".billing.tax,
"public".billing.creatorResult: id | guest_id |
trx_date | trx_time | depart | payment_method | tax|tax_amount |
billing_amount | total | amount_paid |
creator+--++--+++-+++---+-+-
1 | 1 | 2003-09-28 | 16:08:52 |
Resto | Cash
| 10 |3600| 36000. | 32400
| 2.00 | middink 2
| 1 | 2003-09-29 | 07:50:17 |
Resto | Visa
| 10 |1200| 12000. | 10800
| 1.00 | middinkbut i have another problem
: - how to simple below statment :
(SUM(("public".items.price
*"public".billing_items.quantity)-("public".billing_items.discount))
* ("public".billing.tax/100)) AS
tax_amount, (SUM(("public".items.price *
"public".billing_items.quantity))) AS
billing_amount, (SUM(("public".items.price *
"public".billing_items.quantity)) -
(SUM(("public".items.price
*"public".billing_items.quantity))*("public".billing.tax/100))
) AS total,- I have discount in
public".billing_items.discount, how to including thediscount to
billing_amount- Original Message - From: "Oliver Elphick"
<[EMAIL PROTECTED]>To:
"Muhyiddin A.M Hayat" <[EMAIL PROTECTED]>Cc: <[EMAIL PROTECTED]>Sent: Monday, September 29, 2003 3:56 AMSubject: Re: [SQL]
SUM() & GROUP BY> Any items in the select list need to be
aggregated (e.g.> SUM("public".items.price)) or mentioned in the GROUP BY
list. Suppose> there are several billing.guest_id values for each
billing.id; which> value should be listed in the
output?>>> -- > Oliver
Elphick
[EMAIL PROTECTED]> Isle of Wight,
UK
http://www.lfix.co.uk/oliver> GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34
3E1D
0C1C>
>
"Blessed is the man that endureth temptation; for
when> he is tried, he shall receive
the crown of life, which> the Lord
hath promised to them that love
him.">
James 1:12>
billing.sql
Description: Binary data
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://archives.postgresql.org
Re: [SQL] Temporary tables
Hi, Try the below steps .. 1. Write one procedure to create tempory table (generic purpose) ** CREATE OR REPLACE FUNCTION SP_CREATE_TEMP_TABLE () RETURNS VARCHAR AS ' DECLARE L_SchemaName name; BEGIN EXECUTE ''CREATE TEMPORARY TABLE temp_table_gen (X VARCHAR);''; SELECT schemaname INTO L_SchemaName FROM pg_stat_user_tables where relname =''temp_table_gen''; RETURN L_SchemaName; END; ' LANGUAGE 'plpgsql'; ** 2. Call the above (generic) procedure to get the temporary table schema name.. by using that schema name ..you can check whether the (real) temporary table is exists or not. ** select into L_SchemaName * from SP_CREATE_TEMP_TABLE(); -- get the schemaname execute ''drop table temp_table_gen;''; -- drop the temptable select schemaname into L_Schema from pg_stat_user_tables where relname=''temp_total_count'' and schemaname =||L_SchemaName||; if (L_Schema is null) then EXECUTE ''CREATE TEMPORARY TABLE temp_total_count (TOTAL_COUNT NUMERIC);''; ELSE EXECUTE ''DELETE FROM temp_total_count;''; END IF; ** I hope this will help u to solve these temporary table issues.. With Regards Vijay From: "George A.J" <[EMAIL PROTECTED]> To: [EMAIL PROTECTED] Subject: [SQL] Temporary tables Date: Sat, 27 Sep 2003 06:31:39 -0700 (PDT) hi, I am using postgresql 7.3.2. Is there any function to determine whether a table exists in the database.Or is there any function that returns the current temp schema. I am using a pl/pgsql function that create and drop a temporary table. The procedure run correctly for the first time for each database connection. If I run the same procedure second time in the same connection it produces the error "ERROR: pg_class_aclcheck: relation 219389 not found WARNING: Error occurred while executing PL/pgSQL function testFun WARNING: line 20 at SQL statement " Here is the function - CREATE OR REPLACE FUNCTION testFun( varchar(10) ) RETURNS setof int AS ' DECLARE --Aliases for parameters vSBAcNo ALIAS FOR $1; --local variables vRow RECORD; BEGIN -- create a tempory table to hold the numbers CREATE TABLE tempTable ( testNo int ) ; for vRow IN select Entryno from EntryTable LOOP return next vRow.Entryno; insert into tempTable values( vRow.Entryno); end loop; drop table tempTable; return; END;' LANGUAGE 'plpgsql'; - If i commented the "insert into tempTable values( vRow.Entryno);" line the function works correctly. The problem is the oid of tempTable is kept when the function is first executed. the next execution creates another table with different oid. So the insert fails. I want to check whether the temporary table exist. If exist do not create the temporary table in subsequent calls and do not dorp it. This will solve the problem. When i searched the pg_class i found the temp table name more than once. ie, a temporary table is created for each connection.I cannot distingush the temp tables. But the tables are in different schema. Is there a method to get the current temporary schema? How postgres distinguish this temp tables?.Is there a way to distinguish temporary tables. The entries in pg_class table is same except the schema. When i used the current_schema() function it returns public. There is a lot of functions that uses temporary tables. I think that there is an option when creating temp tables in postgres 7.4 . But no way to use 7.4 now it is a working database. can i write a function to check the existance of the temporary table... please help... jinujose - Do you Yahoo!? The New Yahoo! Shopping - with improved product search _ Keep up with the pace of change. Register for My Tech Ed. http://server1.msn.co.in/sp03/teched/index.asp Realise your potential! ---(end of broadcast)--- TIP 8: explain analyze is your friend
