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')
' as the escaping sequence but also tried to use it in the string. I suggest using $$ instead.plpgsql lang, you are missing BEGIN .. ENDSo 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;
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.
