concat in postgresql

Question

I created a function using postgresql. But this function returns error when i try to execute it. error "ERROR: syntax error at or near "||"

CREATE OR REPLACE FUNCTION search_address_book(address_ids character)
  RETURNS void AS
$BODY$ 
SET sqlStatement = 'select * from addressbook';
SET address_ids = 'Post' || 'greSQL';
$BODY$
LANGUAGE 'sql' STABLE
COST 100;

My Server version is 8.3.14. Is this wrong? how do i concat two strings in postgresql?

Note that using quoted language names 'sql' or 'plpgsql'is unsupported and will not work any longer when moving to 9.1 or higher. You should specify the language without single quotes. — user330315
– user330315, Commented Jan 10, 2013 at 13:09
@a_horse_with_no_name: Good advice. But to be precise, I quote the manual: For backward compatibility, the name can be enclosed by single quotes. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 10, 2013 at 14:15
@ErwinBrandstetter: it seems it will go away with the next version: postgresql.1045698.n5.nabble.com/… — user330315
– user330315, Commented Jan 10, 2013 at 14:24
I would love to see strings disallowed for the identifier, and I know they really want to. But I doubt it will happen for 9.3. Too many people would whine about broken code. Check out the devel manual here. Still the same disclaimer. The case you link to deals with the fact that 'PLpgSQL' no longer works, because lower casing has been removed. — Erwin Brandstetter
– Erwin Brandstetter, Commented Jan 10, 2013 at 14:34

Erwin Brandstetter · Accepted Answer · 2013-01-10 14:40:27Z

6

To use variables you need a plpgsql function:

CREATE OR REPLACE FUNCTION search_address_book(address_ids character)
  RETURNS void AS
$BODY$
declare
   sqlStatement text;
   address_ids text;
begin
   sqlStatement := 'select * from addressbook';
   address_ids := 'Post' || 'greSQL';
end
$BODY$
LANGUAGE plpgsql STABLE;

edited Jan 10, 2013 at 14:40

Erwin Brandstetter

668k159 gold badges1.2k silver badges1.3k bronze badges

answered Jan 10, 2013 at 12:44

Clodoaldo Neto

127k30 gold badges250 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Erwin Brandstetter Over a year ago

Use LANGUAGE plpgsql without single quotes. It's an identifier, quoting is discouraged and only tolerated for backward compatibility.

Erwin Brandstetter Over a year ago

Upvoted. :) And I took the liberty to remove the redundant declare key word.

Collectives™ on Stack Overflow

concat in postgresql

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related