Concatenating strings within a function definition

Started by Nishad Prakashover 21 years ago2 messagesbugs
Jump to latest
#1Nishad Prakash
prakashn@uci.edu

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

#2Tom Lane
tgl@sss.pgh.pa.us
In reply to: Nishad Prakash (#1)
Re: Concatenating strings within a function definition

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