[SOLVED] How to remove comma, double quotes in postgresql output

Issue

I have two columns: URL and Name. I i need to concatenate to columns in one variable,
but in the output i want to remove the double quotes and comma.

 id |          url           |      name      | description | last_update
----+------------------------+----------------+-------------+-------------
 18 | http://www.oreilly.com | O'Reilly Media |             |
 19 | https://www.google.com | Google         |             | 2013-06-01
(2 rows)
create or replace Function hello_world()
RETURNS text AS
$$
DECLARE 
v_name_url VARCHAR;
BEGIN
select (name, url) INTO v_name_url from links e where id = 18;

RETURN v_name_url;
END
$$ LANGUAGE plpgsql;

Output

                 hello_world
----------------------------------------------
 ("O'Reilly Media",http://www.oreilly.com)
(1 row)

How to remove the double quotes and comma from the output?

            hello_world
--------------------------------------
 O'Reilly Media http://www.oreilly.com
(1 row)

Solution

You can change your stored procedure to something like this:

drop function hello_world();

create or replace function hello_world()
RETURNS text AS
$$
DECLARE 
v_name_url VARCHAR;
BEGIN
select concat(name, ' ', url) INTO v_name_url from links e where id = 18;

RETURN v_name_url;
END
$$ LANGUAGE plpgsql;

Your results will be like so:

select hello_world();

              hello_world              
---------------------------------------
 O'Reilly Media http://www.oreilly.com
(1 row)

Answered By – zedfoxus

Answer Checked By – David Marino (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *