Thread

1. Concatenating strings within a function definition

   Nishad Prakash <prakashn@uci.edu> — 2004-11-04T19:18:56Z

   I'm trying to create a function like so:
   
   create function get_lid_prefix (text) returns setof int as
   'select lid from ilemma where lemma ~ \'^\' + upper($1)'
   language 'sql';
   
   I've tried several variations, including
   
   'select lid from ilemma where lemma ~ ' || '^' || ' upper ($1)'
   
   'select lid from ilemma where lemma ~ (cast ''^'' as text) || upper ($1)'
   
   
   but nothing works.  Can I do this at all?  The idea is that if $1 is
   "foo", the query should be
   
       select lid from ilemma where lemma ~ '^FOO'
   
   I'm running PostgreSQL 7.4 on sparc-sun-solaris2.8, compiled by GCC 3.0.4.
   
   Thanks,
   
   Nishad
   -- 
   "Underneath the concrete, the dream is still alive" -- Talking Heads
   
   
   

2. Re: Concatenating strings within a function definition

   Tom Lane <tgl@sss.pgh.pa.us> — 2004-11-04T19:32:42Z

   Nishad Prakash <prakashn@uci.edu> writes:
   > I'm trying to create a function like so:
   
   > create function get_lid_prefix (text) returns setof int as
   > 'select lid from ilemma where lemma ~ \'^\' + upper($1)'
   > language 'sql';
   
   'select lid from ilemma where lemma ~ (\'^\' || upper($1))'
   
   			regards, tom lane