postgresql concat error when used in function

Question

PostgreSQL syntax error when using CONCAT in user defined function. Using DbVisualizer as SQL client.

CREATE FUNCTION t() RETURNS TEXT 
AS ' SELECT CONCAT('some', '_text') ; ' 
LANGUAGE plpgsql;

[Code: 0, SQL State: 42601] ERROR: syntax error at or near "some" Position: 55 [Script position: 54 - 61]

Query works as expected SELECT CONCAT('some_', '_text')

Answer 1

1. You've used ' as the escaping sequence but also tried to use it in the string. I suggest using $$ instead.
2. With plpgsql lang, you are missing BEGIN .. END

So the corrected SQL is:

CREATE FUNCTION t() RETURNS TEXT
AS $$
BEGIN
    SELECT CONCAT('some', '_text');
END;
$$ LANGUAGE plpgsql;

Or with sql as language:

CREATE FUNCTION t() RETURNS TEXT
AS $$
    SELECT CONCAT('some', '_text');
$$ LANGUAGE sql;

Answer 2

Consider this an addon to the answer by @Orkhan Alikhanov.

The reason it doesn't work in DbVisualizer is because that program (for some unknown reason) does not allow (difficult?) statements. You can fix this by turning on what they call "dialects" in the settings:

Then, both suggestions in the answer by Orkhan should work.