Thread: how to concatenate in PostgreSQL

Rehan Saleem <pk_rehan@yahoo.com> wrote:

> hi ,
> how can we concatinate these lines and execute sql command
>  
>        set sql = 'select user,username, firstname '
>       set sql += ' lastname, cardno from table1 where userid=' + 5

sql = sql || ' bla fasel';

|| is the concat - Operator.



Andreas
-- 
Really, I'm not out to destroy Microsoft. That will just be a completely
unintentional side effect.                              (Linus Torvalds)
"If I was god, I would recompile penguin with --enable-fly."   (unknown)
Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°

On 3/24/12, Andreas Kretschmer <akretschmer@spamfence.net> wrote:
> Rehan Saleem <pk_rehan@yahoo.com> wrote:
>
>> hi ,
>> how can we concatinate these lines and execute sql command
>>
>>        set sql = 'select user,username, firstname '
>>       set sql += ' lastname, cardno from table1 where userid=' + 5
>
> sql = sql || ' bla fasel';
>
> || is the concat - Operator.
>
>
>
> Andreas
> --
> Really, I'm not out to destroy Microsoft. That will just be a completely
> unintentional side effect.                              (Linus Torvalds)
> "If I was god, I would recompile penguin with --enable-fly."   (unknown)
> Kaufbach, Saxony, Germany, Europe.              N 51.05082°, E 13.56889°
>
> --
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>


You may want to ensure that none of variables to be concatenated is
NULL, else the end result will be NULL.
"coalesce" function provides means to provide an substitute value for
a variable that may be NULL.

Allan.

Hi,

Probably you're looking for these set of articles.

http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Microsoft_SQL_Server

The second article (by Ethan) has good small hints for things such as
the query that you ask in this thread, when migrating from MSSQL to
PostgreSQL.

--
Robins

On 03/24/2012 05:13 PM, Rehan Saleem wrote:
> hi ,
> how can we concatinate these lines and execute sql command
> setsql = 'select user,username, firstname '
> set sql += ' lastname, cardno from table1 where userid=' + 5
> exec(sqi)
> where 5 is the userid from table1
> thanks
>

On 03/24/2012 04:43 AM, Rehan Saleem wrote:

hi ,

how can we concatinate these lines and execute sql command

In what? Psql? A PL/pgSQL function. C/Java/PHP/Python/Perl/Erlang/Lua?

 

       set sql = 'select user,username, firstname '

      set sql += ' lastname, cardno from table1 where userid=' + 5

      exec(sqi)

     where 5 is the userid from table1

thanks

 

 

Cheers,
Steve

On 03/27/2012 07:48 AM, Rehan Saleem wrote:

well i am quite sure its PostgreSQL forum and it is obvious, i am asking this to concatenate in plpgsql.

---

From: Steve Crawford <scrawford@pinpointresearch.com>
To: pgsql-sql@postgresql.org
Sent: Monday, March 26, 2012 9:08 PM
Subject: Re: [SQL] how to concatenate in PostgreSQL

On 03/24/2012 04:43 AM, Rehan Saleem wrote:

hi ,

how can we concatinate these lines and execute sql command

In what? Psql? A PL/pgSQL function. C/Java/PHP/Python/Perl/Erlang/Lua?

 

       set sql = 'select user,username, firstname '

      set sql += ' lastname, cardno from table1 where userid=' + 5

      exec(sqi)

     where 5 is the userid from table1

thanks

 

 

Cheers,
Steve

Yes, after about a decade of PostgreSQL use and forum participation I think I know which forum this is. Sarcasm and biting at people who want to help you is not a good way to make friends and get help on the forums.

If you meant PL/pgSQL then say it. It is not the same as SQL though the syntax is generally similar. Look at DECLARE and BEGIN for starters. I've seen too many threads get reset back to the start once some incorrect assumptions get corrected to feel that up-front clarification is a waste of time. http://wiki.postgresql.org/wiki/Guide_to_reporting_problems is more oriented to problem reporting than general questions but has helpful advice nonetheless.

Back to the issue at hand...others have pointed out the || operator and the issue with NULL. It appears you are doing a lot of conversion requiring writing of PL/pgSQL functions. Spend a few minutes skimming:
http://www.postgresql.org/docs/current/static/plpgsql.html

As to the topic of executing the command including many examples see:
http://www.postgresql.org/docs/current/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN

If you are writing functions that must use dynamically generated table and column names, become familiar with the quote_ident, quote_literal and quote_nullable functions.

Cheers,
Steve