Re: [SQL] Does IMMUTABLE property propagate?

[Tom Lane]

Petru Ghita  writes:
> "..immediately replaced with the function value" doesn't mean that the
> results of a previously evaluated function for the same parameters are
> stored and reused?

No, it means what it says: the function is executed once and replaced
with a constant representing the result value.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Drop all constraints

[Andreas Kretschmer]

Gianvito Pio  wrote:

> Hi all,
> is there a way to drop all constraints of a table?
>
> I found this workaround in the manual:
>
> CREATE TABLE temp AS SELECT * FROM distributors;
> DROP TABLE distributors;
> CREATE TABLE distributors AS SELECT * FROM temp;
> DROP TABLE temp;

I think, you can change the table-name from temp to distributors:
(alter table temp rename to distributors), it's cheaper.

>
> Is there any other way to do it?

Maybe. I think you can walk through the system catalogs to find all
constraints for a table and drop them (alter table foo drop constraint
...). I think, you should start with
http://www.postgresql.org/docs/8.4/interactive/catalog-pg-constraint.html




Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.  (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.  N 51.05082°, E 13.56889°

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Drop all constraints

[Gianvito Pio]

Hi all,
is there a way to drop all constraints of a table?

I found this workaround in the manual:

CREATE TABLE temp AS SELECT * FROM distributors;
DROP TABLE distributors;
CREATE TABLE distributors AS SELECT * FROM temp;
DROP TABLE temp;

Is there any other way to do it?
Thanks

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Does IMMUTABLE property propagate?

[Petru Ghita]

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
 
Documentation states:

IMMUTABLE indicates that the function cannot modify the database and
always returns the same result when given the same argument values;
that is, it does not do database lookups or otherwise use information
not directly present in its argument list. If this option is given,
any call of the function with all-constant arguments can be
immediately replaced with the function value.


"..immediately replaced with the function value" doesn't mean that the
results of a previously evaluated function for the same parameters are
stored and reused?

The problem here is exactly about evaluating the expression several
times as the result is exactly the same for all the columns in the query.

Greg Stark wrote:
>
> The immutable property had nothing to do with caching results.
> Postgres never caches the results of functions. The immutable
> property is used top determine if it's safe to use indexes or other
>  plans that avoid evaluating an expression repeatedly.
>
>> On 6 Mar 2010 02:45, "Petru Ghita" > > wrote:
>>
> Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
> IMMUTABLE, does the query planner cache the result of f3 and reuse
> it or if you want to get a little more speed you better explicitly
> define yourself f3 as IMMUTABLE?
>
> I had an aggregate query like:
>
> select id, sum(p1*f1(a)/f2(b) as r1, sum(p2*f1(a)/f2(b) as r2, ...
> sum(pn*f1(a)/f2(b) as rn
>
> ... group by id;
>
> Where f1(x) and f2(x) were defined as IMMUTABLE.
>
> By the experiments I ran looks like after defining a new function
> f3(a,b):= f1(a)/f2(b) and rewriting the query as:
>
> select id, sum(p1*f3(a,b) as r1, sum(p2*f3(a,b) as r2, ...
> sum(pn*f3(a,b) as rn
>
> ... group by id;
>
> *Looks like* I got a little (5%) improvement in performance of the
> query. Is there a way to find out if the function is re-evaluated
> each time? Is this the recommended way to proceed?
>
> Thank you!
>
> Petru Ghita
>>
>>
- --
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org
)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/
 
iEYEARECAAYFAkuSf+0ACgkQt6IL6XzynQSREQCfQsZpH/cWzMTqVBv4/2D4X+Ib
uBYAniJwbox3bPA4dG/x4vmr0FY+icO9
=8Rvn
-END PGP SIGNATURE-


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Assigning NEW. anomoly

[Little, Douglas]

Hello,

I have a trigger function designed to encrypt source data on insert/update.
I have a problem where an assignment isn't happening, and I don't understand 
why.
Any thoughts

In the function, I unnecessarily reset new.pii_ccard_number to null.
It must be null already for the else condition to apply.
The problem is that the following statement  NEW.pi2_pii_ccard_number=null;
Doesn't appear to be executing.  After the statement completes, the pi2 column 
Isn't set to null.

However when I change the function and remove the highlighted line it works 
setting pi2 to null.
Really weird.
Thanks
Doug


CREATE OR REPLACE FUNCTION bop.amex_ccr_settlement_encrypt()
  RETURNS "trigger" AS
$BODY$ BEGIN If NEW.pii_ccard_number is not null then
 
NEW.pi2_pii_ccard_number=dba_work.owwencrypt(new.pii_ccard_number,new.amex_ccr_settlement_id);
 NEW.pii_ccard_number='';
 else
 NEW.pi2_pii_ccard_number=null;
 NEW.pii_ccard_number=null;
end if; RETURN NEW; END; $BODY$
  LANGUAGE 'plpgsql' VOLATILE;

CREATE TRIGGER amex_ccr_settlement_encrypt_0306bak
  BEFORE INSERT OR UPDATE
  ON bop.amex_ccr_settlement_0306bak
  FOR EACH ROW
  EXECUTE PROCEDURE bop.amex_ccr_settlement_encrypt();
COMMENT ON TRIGGER amex_ccr_settlement_encrypt ON bop.amex_ccr_settlement IS 
'version:20100305_0912 generated on 2010-03-06 08:01:57.836201-06';

update  bop.amex_ccr_settlement_0306bak a
set pii_ccard_number = null
from bop.amex_ccr_settlement_keys b
where  b.amex_ccr_settlement_id = a.amex_ccr_settlement_id and 
pi2_pii_ccard_number is not null and b.pii_ccard_number is null
;

Doug Little
Sr. Data Warehouse Architect | Enterprise Data Management | Orbitz Worldwide
500 W. Madison, Suite 1000  Chicago IL 60661| Office 312.260.2588 | Fax 
312.894.5164 | Cell 847-997-5741
douglas.lit...@orbitz.com

 [cid:image001.jpg@01CABD15.EE7F1830]   orbitz.com | 
ebookers.com | 
hotelclub.com | 
cheaptickets.com | 
ratestogo.com | 
asiahotels.com

<>

Re: [SQL] Does IMMUTABLE property propagate?

[Greg Stark]

The immutable property had nothing to do with caching results. Postgres
never caches the results of functions. The immutable property is used top
determine if it's safe to use indexes or other plans that avoid evaluating
an expression repeatedly.

On 6 Mar 2010 02:45, "Petru Ghita"  wrote:

-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1

Given f1(x) as IMMUTABLE and f2(x) as IMMUTABLE, and f3(f1,f2) as
IMMUTABLE, does the query planner cache the result of f3 and reuse it
or if you want to get a little more speed you better explicitly define
yourself f3 as IMMUTABLE?

I had an aggregate query like:

select id,
  sum(p1*f1(a)/f2(b) as r1,
  sum(p2*f1(a)/f2(b) as r2,
  ...
  sum(pn*f1(a)/f2(b) as rn

...
group by id;

Where f1(x) and f2(x) were defined as IMMUTABLE.

By the experiments I ran looks like after defining a new function
f3(a,b):= f1(a)/f2(b) and rewriting the query as:

select id,
  sum(p1*f3(a,b) as r1,
  sum(p2*f3(a,b) as r2,
  ...
  sum(pn*f3(a,b) as rn

...
group by id;

*Looks like* I got a little (5%) improvement in performance of the
query. Is there a way to find out if the function is re-evaluated each
time?
Is this the recommended way to proceed?

Thank you!

Petru Ghita
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.4.9 (MingW32)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org/

iEYEARECAAYFAkuRwYQACgkQt6IL6XzynQTHEgCffi2QMWkkvTIsuglsanvcUyRB
I+wAoKr22B7FJJVDCssGKGwB8zr4NjQG
=V/BS
-END PGP SIGNATURE-


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] an aggregate to return max() - 1 value?

[Louis-David Mitterrand]

On Thu, Mar 04, 2010 at 08:53:10PM +, Greg Stark wrote:
> SELECT col FROM tab ORDER BY col DESC OFFSET 1 LIMIT 1
> 
> In 8.4 OLAP window functions provide more standard and flexibility
> method but in this case it wouldn't perform as well:
> 
> postgres=# select i from (select i, rank() over (order by i desc) as r
> from i) as x where r = 2;
>  i
> 
>  99
> (1 row)
> 
> postgres=# select i from (select i, dense_rank() over (order by i
> desc) as r from i) as x where r = 2;
>  i
> 
>  99
> (1 row)

Wow, I didn't know about window functions until now. It's exactly what I
need.

Thanks Greg, and also thanks to others who sent their suggestion.

Cheers,

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Check type compatibility

[Little, Douglas]

Hello,
I believe types are compatible if they can be cast automatically.
The pg_cast table record all possible casts between types.   If it castcontext 
is 'a' then I belive it's an automatic conversion which is what I think you 
want.  'i' implicit means that cast is possible, but must be explicitly cast. 

I suspect that you'll need to write a function that consults the table for the 
type pair. 

Cheers
Doug



-Original Message-
From: pgsql-sql-ow...@postgresql.org [mailto:pgsql-sql-ow...@postgresql.org] On 
Behalf Of Gianvito Pio
Sent: Saturday, March 06, 2010 5:04 AM
To: pgsql-sql@postgresql.org
Subject: [SQL] Check type compatibility

Hi all,
is there a way to check if two attribute are type compatible (for 
example integer and serial, integer and integer, character varying and 
text, etc..)?

Example:
IF (compatible (table1.att1, table2.att2)) THEN
 ...
ELSE
 ...
END IF;

Thanks


-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Check type compatibility

[Nilesh Govindarajan]

On Sat, Mar 6, 2010 at 4:34 PM, Gianvito Pio  wrote:

> Hi all,
> is there a way to check if two attribute are type compatible (for example
> integer and serial, integer and integer, character varying and text, etc..)?
>
> Example:
> IF (compatible (table1.att1, table2.att2)) THEN
>...
> ELSE
>...
> END IF;
>
> Thanks
>
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>

Explain in more detail.

-- 
Nilesh Govindarajan
Site & Server Administrator
www.itech7.com

[SQL] Check type compatibility

[Gianvito Pio]

Hi all,
is there a way to check if two attribute are type compatible (for 
example integer and serial, integer and integer, character varying and 
text, etc..)?


Example:
IF (compatible (table1.att1, table2.att2)) THEN
...
ELSE
...
END IF;

Thanks


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql