Skip site navigation (1) Skip section navigation (2)

Re: Substitute a variable in PL/PGSQL.

From: Steve Martin <steve(dot)martin(at)nec(dot)co(dot)nz>
To: "Roberts, Jon" <Jon(dot)Roberts(at)asurion(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Substitute a variable in PL/PGSQL.
Date: 2008-07-27 23:04:56
Message-ID: 488CFF18.3080707@nec.co.nz (view raw or flat)
Thread:
Lists: pgsql-general
Roberts, Jon wrote:

>>What I am trying to do is find the difference between two tables, one
>>that stores the
>>information in a single column, and the other which stores the same
>>    
>>
>data
>  
>
>>in multiple
>>columns.
>>
>>E.g.
>>CREATE TABLE test(col1 text, col2 text, col3 text, col4 text, col5
>>    
>>
>text,
>  
>
>>col6 text, col7 text, col8 text, col9 text, col10 text);
>>CREATE TABLE test2(col_data text NOT NULL,  some_data  text NOT NULL,
>>other_data text,
>>                                      CONSTRAINT test2_index PRIMARY
>>    
>>
>KEY(
>  
>
>>                                           col_data,
>>                                           some_data ));
>>
>>Trying to find data set in test2.col_data that is not in test.col1 to
>>test.col10.
>>
>>    
>>
>
>FINALLY you get to the requirements.  Next time, just ask a question
>like the above.  You were asking how to solve a technical problem that
>didn't relate to the actual business need.
>
>Here are three ways to skin this cat.
>
>--version 1
>select col_data from test2
>except
>select coalesce(col1, '') || coalesce(col2, '') || coalesce(col3, '') ||
>
>       coalesce(col4, '') || coalesce(col5, '') || coalesce(col6, '') ||
>
>       coalesce(col7, '') || coalesce(col8, '') || coalesce(col9, '') ||
>
>       coalesce(col10, '')
>  from test
>
>--version 2
>select col_data 
>  from test2 t2
> where not exists (select null 
>                     from test t
>                    where t2.col_data = coalesce(t.col1, '') || 
>                                        coalesce(t.col2, '') || 
>                                        coalesce(t.col3, '') || 
>                                        coalesce(t.col4, '') || 
>                                        coalesce(t.col5, '') || 
>                                        coalesce(t.col6, '') || 
>                                        coalesce(t.col7, '') || 
>                                        coalesce(t.col8, '') || 
>                                        coalesce(t.col9, '') ||
>                                        coalesce(t.col10, ''))
>--version 3
>select t2.col_data
>  from test2 t2
>  left join (select coalesce(col1, '') || coalesce(col2, '') || 
>                    coalesce(col3, '') || coalesce(col4, '') || 
>                    coalesce(col5, '') || coalesce(col6, '') || 
>                    coalesce(col7, '') || coalesce(col8, '') || 
>                    coalesce(col9, '') || coalesce(col10, '') as
>col_data
>               from test) t
>    on t2.col_data = t.col_data
> where t.col_data is null
>
>
>Jon
>  
>
Thanks Jon for the hints.
Steve



In response to

pgsql-general by date

Next:From: Steve MartinDate: 2008-07-27 23:15:31
Subject: Re: Substitute a variable in PL/PGSQL.
Previous:From: Rich ShepardDate: 2008-07-27 22:44:37
Subject: Re: Problems Restarting PostgreSQL Daemon

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group