CODEWITHSUNDEEP
postgresql
Jezen Thomas
Jezen Thomas

Reputation: 13800

Strip parentheses from SQL query

I am tinkering with this snippet of SQL I reappropriated from another SO question:

CREATE OR REPLACE FUNCTION db_to_csv(path TEXT) RETURNS void AS $$
DECLARE
  target RECORD;
  statement TEXT;
BEGIN
  FOR target IN
    SELECT DISTINCT table_name
    FROM information_schema.columns
    WHERE table_schema='public'
    AND position('_' in table_name) <> 1
    ORDER BY 1
  LOOP
    statement := 'COPY '
      || target
      || ' TO '''
      || path
      || '/'
      || target
      || '.csv'
      ||''' DELIMITER '';'' CSV HEADER';
    EXECUTE statement;
  END LOOP;
  return;
end;
$$ LANGUAGE plpgsql;

It very nearly almost works, but the statement it tries to execute looks like:

COPY (Bok_F_Acc) TO '/Users/jgt/dir/(Bok_F_Acc).csv' DELIMIT...

…Whereas I would like the statement to look like:

COPY Bok_F_Acc TO '/Users/jgt/dir/Bok_F_Acc.csv' DELIMIT...

How can I strip those parentheses?

Upvotes: 3

Views: 783

Answers (1)

Marth
Marth

Reputation: 24822

SELECT QUOTE_IDENT(TRIM(BOTH '()' FROM target));

Note that this would remove any number of leading/trailing parentheses.
To remove at most one of each, you could use :

SELECT QUOTE_IDENT(REGEXP_REPLACE(target, E'\^\\(|\\)\$', '', 'g'));

Upvotes: 3

Related Questions