Re: [SQL] Does IMMUTABLE property propagate?

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

Re: [SQL] Drop all constraints

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 cha

[SQL] Drop all constraints

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 pgsq

Re: [SQL] Does IMMUTABLE property propagate?

-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

[SQL] Assigning NEW. anomoly

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

Re: [SQL] Does IMMUTABLE property propagate?

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: -BE

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

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 (o

Re: [SQL] Check type compatibility

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 explicit

Re: [SQL] Check type compatibility

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 >... > EL

[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 l