Re: [SQL] Temporary tables

2003-09-28 Thread vijaykumar M
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 T

Re: [SQL] SUM() & GROUP BY

2003-09-28 Thread Muhyiddin A.M Hayat
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

Re: [SQL] SUM() & GROUP BY

2003-09-28 Thread Muhyiddin A.M Hayat
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.creato

[SQL] SUM() & GROUP BY

2003-09-28 Thread Muhyiddin A.M Hayat
  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".departme

Re: [SQL] Mystery function error

2003-09-28 Thread Tom Lane
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 me

[SQL]

2003-09-28 Thread Dan Langille
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;

Re: [SQL] Mystery function error

2003-09-28 Thread Tom Lane
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 ty

Re: [SQL]

2003-09-28 Thread Tom Lane
"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

Re: [SQL]

2003-09-28 Thread Dan Langille
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\''

Re: [SQL] SUM() & GROUP BY

2003-09-28 Thread Oliver Elphick
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".billin

Re: [SQL]

2003-09-28 Thread Tom Lane
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

Re: [SQL] Mystery function error

2003-09-28 Thread Tom Lane
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 CreateE

Re: [SQL] Mystery function error

2003-09-28 Thread Josh Berkus
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 ---

Re: [SQL] Mystery function error

2003-09-28 Thread Joe Conway
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 wou

Re: [SQL] Mystery function error

2003-09-28 Thread Tom Lane
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 inli