[GENERAL] fulltext search udf

2009-06-10 Thread Andreas Wenk 

Hi everybody,

altough I know that the way over a Trigger is preferred, I can't see what is wrong in my 
function. It must be an escaping error:


CREATE OR REPLACE FUNCTION 
user_insert_vector_data(text,text,text,numeric,numeric,numeric,text)

RETURNS void AS
$$
DECLARE
art_nr ALIAS for $1;
bezeichnung ALIAS FOR $2;
beschreibung ALIAS FOR $3;
preis ALIAS FOR $4;
steuersatz ALIAS FOR $5;
aktionspreis ALIAS FOR $6;
stichworte ALIAS for $7;
vect tsvector;
BEGIN
vect := setweight(to_tsvector('german',coalesce(stichworte,'')),'A') ||
setweight(to_tsvector('german',coalesce(beschreibung,'')),'B');

EXECUTE 'INSERT INTO produkte 
(art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)
VALUES 
('''||art_nr||''','''||bezeichnung||''','''||beschreibung||''','||preis||',

   
'||steuersatz||','||aktionspreis||','''||stichworte||''','||vect||')';

END;
$$ LANGUAGE plpgsql;

Firering this statement:

select 
user_insert_vector_data('adfvb','adfvb','adfvb',15.5,25.50,2,'alpha,beta,cesar');

results in this error:

FEHLER:  Syntaxfehler bei »:«
LINE 3: ... 25.50,2,'alpha,beta,cesar','beta':2A 'adfvb...
 ^
QUERY:  INSERT INTO produkte 
(art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)

VALUES ('adfvb','adfvb','adfvb',15.5,
   25.50,2,'alpha,beta,cesar','beta':2A 'adfvb':4B 'alpha':1A 
'cesar':3A)
CONTEXT:  PL/pgSQL function user_insert_vector_data line 14 at EXECUTE 
statement


vector is of datatype tsvector. So I don't understand why pg is complaining ...

Thank's for any hints and sorry for annoying if I am too blind ...

Cheers

Andy

--
Netzmeister St.Pauli

St.Pauli - Hamburg - Germany

Andreas Wenk


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

Re: [GENERAL] fulltext search udf

2009-06-10 Thread Tom Lane 
Andreas Wenk a.w...@netzmeister-st-pauli.de writes:
  EXECUTE 'INSERT INTO produkte 
 (art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)
  VALUES 
 ('''||art_nr||''','''||bezeichnung||''','''||beschreibung||''','||preis||',
 
 '||steuersatz||','||aktionspreis||','''||stichworte||''','||vect||')';

This is not going to work with such a half-baked approach to quoting the
data values --- any value with ' or \ in it will break it.  You could
use quote_literal(), but I wonder why you are bothering with EXECUTE at
all.  If you just did the INSERT directly it would run faster and be a
lot less ugly.

regards, tom lane

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

Re: [GENERAL] fulltext search udf

2009-06-10 Thread Andreas Wenk 

Tom Lane wrote:

Andreas Wenk a.w...@netzmeister-st-pauli.de writes:
 EXECUTE 'INSERT INTO produkte 
(art_nr,bezeichnung,beschreibung,preis,steuersatz,aktionspreis,stichworte,vector)
 VALUES 
('''||art_nr||''','''||bezeichnung||''','''||beschreibung||''','||preis||',


'||steuersatz||','||aktionspreis||','''||stichworte||''','||vect||')';


This is not going to work with such a half-baked approach to quoting the
data values --- any value with ' or \ in it will break it.  You could
use quote_literal(), but I wonder why you are bothering with EXECUTE at
all.  If you just did the INSERT directly it would run faster and be a
lot less ugly.

regards, tom lane
Tom, thanks for the hint! I was really doing the wrong thing. There have 
been even more errors what made me confused. Now I got it ...


Cheers

Andy

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