From: Edgardo Portal <egportal2002@yahoo.com>
Subject: Re: concatenate question
Date: Thu, 9 Dec 2010 16:16:48 +0000 (UTC)
Organization: A noiseless patient Spider
Lines: 58
Message-ID: <idqvdg$r0q$1@news.eternal-september.org>
References: <1291757825.16970.18.camel@tony1.localdomain>
	<AANLkTi=cdwqU0DtFf68BuD=wb3tWfCToe4hn_AUWJFpQ@mail.gmail.com>
	<1291758466.16970.19.camel@tony1.localdomain>
	<AANLkTin+GMRqqQqisMhnFCaqQi3xbv7SwBOQQ4RjHLpx@mail.gmail.com>
	<1291817313.1654.2.camel@tony1.localdomain>
Injection-Date: Thu, 9 Dec 2010 16:16:48 +0000 (UTC)
Injection-Info: mx01.eternal-september.org;
	posting-host="Th+1vjsZX3CHw95bOGBd9A";
	logging-data="27674"; mail-complaints-to="abuse@eternal-september.org";
	posting-account="U2FsdGVkX19aobENS33JWf0b1lwCSsdxOf1NZQyjt9A="
User-Agent: slrn/pre1.0.0-16 (Linux)
Cancel-Lock: sha1:3ZFrT01O3XxmtxuPOa8JeTZ95Ro=
To: pgsql-sql@postgresql.org

On 2010-12-08, Tony Capobianco <tcapobianco@prospectiv.com> wrote:
> Thanks so much to everyone for your responses.  You've been very
> helpful.  I'm running PostGres 8.4 and we're migrating our datawarehouse
> from Oracle 10.2.  I guess datatype is implicitly cast in oracle under
> these circumstances:
>
> SQL> create table tony_test as select memberid||addeddate "data" from
> TMPSV_PARENT_MASTER where rownum < 5;
>
> Table created.
>
> SQL> desc tony_test
>  Name                                      Null?    Type
>  ----------------------------------------- --------
> ----------------------------
>  data                                               VARCHAR2(59)
>
> SQL> select * from tony_test;
>
> data
> -----------------------------------------------------------
> 3812482212010-06-23 13:53:38
> 3812510902010-06-23 14:12:25
> 3812622482010-06-23 15:24:45
> 3812725152010-06-23 16:35:24
>
>
> Thanks!

FWIW, I think you can also extend the built-in concatenate operator, though
I personally haven't used something like this in a production DB:

BEGIN TRANSACTION ;

CREATE FUNCTION concat_num_ttwotz(numeric, timestamp without time zone)
  RETURNS text AS
  'select $1::text || $2::text;'
  LANGUAGE SQL
  IMMUTABLE
  RETURNS NULL ON NULL INPUT ;

CREATE OPERATOR || (
  PROCEDURE = concat_num_ttwotz
 ,LEFTARG = numeric
 ,RIGHTARG= timestamp without time zone
) ;

CREATE TABLE tony_test (
  memberid   numeric
 ,addeddate  timestamp without time zone
) ;

INSERT INTO tony_test VALUES(1,CURRENT_DATE) ;

SELECT memberid || addeddate FROM tony_test ;

ROLLBACK ;