Re: || operator

2014-09-04 11:13 GMT+02:00 Vinayak <vinpokale(at)gmail(dot)com>:

> Hi,
>
> The || operator with arguments (character,character) works fine and even ||
> operator(character,varchar) also works fine.
> but || operator is not working as expected with arguments character data
> type and any other data type like integer,smallint,date,text.
> Example:
> postgres=# select 'ab'::char(10) || 4::int;
> ?column?
> ----------
> ab4
> (1 row)
> postgres=# select 'ab'::char(10) || 'a'::text;
> ?column?
> -------------
> aba
> (1 row)
>
> so I have created || operator with argument character and anyelement.
> Example:
> create or replace function concat_character(character, anyelement) returns
> text as $$ select concat($1,$2)$$ language sql;
> create operator || (procedure = concat_character, leftarg = character,
> rightarg = anyelement);
> it works fine with argument of type int,smallint,bigint,date etc.
> but its not working with text and varchar data type.
> Example:
> postgres=# select 'ab'::char(10) || 4::int;
> ?column?
> -------------
> ab 4
> (1 row)
>
> postgres=# select 'ab'::char(10) || 'b'::text;
> ?column?
> ----------
> abb
> (1 row)
>

text is more general -- it it does cast to text - there is not || operator
for leftarg character and righarg text

>
> postgres=# select 'ab'::char(10) || 'b'::varchar(5);
> ERROR: operator is not unique: character || character varying
> LINE 1: select 'ab'::char(10) || 'b'::varchar(5);
> ^
> HINT: Could not choose a best candidate operator. You might need to add
> explicit type casts.
> Thought?
>
>
Not too much - it is limit of Postgres type system :(

Pavel

>
> -----
> Regards,
> Vinayak,
>
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/operator-tp5817541p5817712.html
> Sent from the PostgreSQL - general mailing list archive at Nabble.com.
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>