Re: [SQL] [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them

2010-07-26 Thread Torsten Zühlsdorff 

Hey Dmitriy,

thanks for your reply.


I think, its would be better to use rule on update instead of the trigger
in such case as you.


I've played the whole weekend with the rule-system, but it didn't work 
for my case. I have a dynamic trigger, which takes cares about revision 
of rows for every table, it is called from. It looks like that:


CREATE OR REPLACE FUNCTION versionizeContent()
RETURNS TRIGGER
AS $$
BEGIN

  /* add new version in central register and insert new row */
  NEW.revision := addContentRevision (OLD.content_id, OLD.revision, 
sessval('user_id')::int));


  EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_NAME) ||
  ' SELECT (' || QUOTE_LITERAL(NEW) || '::' || 
quote_ident(TG_TABLE_NAME) ||').*' ;


  RETURN NULL;

END;
$$ LANGUAGE 'plpgsql' VOLATILE;

Even if i drop the dynamic INSERT-Part and write it for every relation, 
i wasn't able to figured out how to manipulate the NEW-Record.


The best i tried so far was:
CREATE RULE "versionize"
AS ON UPDATE
TO templates
DO INSTEAD
(
  SELECT addContentRevision (OLD.content_id, OLD.revision, 
sessval('user_id')::int) INTO NEW.revision;

  INSERT INTO templates SELECT NEW.* RETURNING *;
);

But an Updates ends with the ERROR:
"ERROR:  schema "*NEW*" does not exist"

Has anyone a hint how to manipulate the NEW record within an RULE?

Thanks,
Torsten


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

[SQL] Returning only alphanumeric values from a query

2010-07-26 Thread Enrique Palacios 
HI everyone,

Im new here, this is my first mail and i would like to know how return from
a query only alphanumeric values and these symbols: ().,-%
Something like:
SELECT description FROM products WHERE productid = 5

RESULT
==
X# PRODUCT %1.0/30(UN)

DESIRED RESULT
==
X PRODUCT %1.0 30(UN)

I read some info, but it didnt worked out for me using regular expressions

excuse my poor english
thank you very much

-- 
Enrique Palacios
*RRHH - Informática
Farmacias RedFarma Calama*

Re: [SQL] Returning only alphanumeric values from a query

2010-07-26 Thread Osvaldo Kussama 
2010/7/26 Enrique Palacios :
> HI everyone,
> Im new here, this is my first mail and i would like to know how return from
> a query only alphanumeric values and these symbols: ().,-%
> Something like:
> SELECT description FROM products WHERE productid = 5
> RESULT
> ==
> X# PRODUCT %1.0/30(UN)
> DESIRED RESULT
> ==
> X PRODUCT %1.0 30(UN)
> I read some info, but it didnt worked out for me using regular expressions
> excuse my poor english
> thank you very much
> --


Try:
bdteste=# SELECT regexp_replace('X# PRODUCT
%1.0/30(UN)',E'[^\\w\\s().,%-]',' ','g');
 regexp_replace

 X  PRODUCT %1.0 30(UN)
(1 row)

http://www.postgresql.org/docs/current/interactive/functions-matching.html#FUNCTIONS-POSIX-REGEXP

Osvaldo

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