current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.
up vote 1 down vote favorite

From within a bash script I tried to use a replace and a detection on a field containing a pipe like:

IF position ('|' in var) IS NOT NULL THEN ...

...REPLACE(field, '|', '#')

The data itself was loaded into the DB using the COPY statement e.g. 

COPY records FROM '$datasource' WITH DELIMITER AS ',' CSV HEADER NULL as '';

I am using psql (9.1.9) and the default field separator is pipe:

#> \f
#> Field separator is "|".

Ideally I would like to SET the default limiter in a CREATE OR REPLACE function at runtime like:

...
END; \$\$ LANGUAGE plpgsql SET config_param TO '#';

OR as a seperate statement just like SET config_param TO '#';

I searched the documentation and internet for the right name of the config parameter without luck. Any help appreciated.

share|improve this question
1  
Do you want to change the delimiter when runing DDL statements, the delimiter used when displaying data or the column delimiter when importing data via COPY? –  a_horse_with_no_name Jul 8 '13 at 14:24
    
The COPY statement which loads the data into proper fields should not be touched because it is a comma separated file. But for the given example fields can contain pipe-delimiters. These strings are shown correctly in the fields, but working on them with replace or to detect the pipe using POSITION does not work. So I would like to use SET in my function at least or call set from within a bash script. Example: function afunction { <<EOF SET config_param TO '#'; BEGIN; .... COMMIT; EOF } –  Tom Jul 8 '13 at 14:37
add comment

1 Answer

up vote 1 down vote accepted

This:

`IF position ('|' in var) IS NOT NULL`

is assuming that the result of position('|' in 'nopipes') is NULL. It is not, it's zero. position returns null only on null input.

regress=> select position('|' in 'abc|def|ghi');
 position 
----------
        4
(1 row)

regress=> select position('|' in 'abcdefghi');
 position 
----------
        0
(1 row)

regress=> select position('|' in null);
 position 
----------

(1 row)

... so if you instead wrote:

IF position ('|' in var) <> 0

it should work fine.

The field separator \f in psql is irrelevant to this. It's only used to control how psql displays result sets. The server and functions on the server cannot see or be affected by this value. \f is just shorthand for \pset fieldsep, it's a psql-local control to choose the field separator for unaligned output:

regress=> \pset format unaligned
Output format is unaligned.
regress=> SELECT 1,2,3;
?column?|?column?|?column?
1|2|3
(1 row)
regress=> \pset fieldsep #
Field separator is "#".
regress=> SELECT 1,2,3;
?column?#?column?#?column?
1#2#3
(1 row)
share|improve this answer
    
Thank you, Craig. Yes indeed: position('|' in 'nopipes') should return zero. For the sake of completeness: show all; lists all config parameters and there is no config parameter to change the default limiter. –  Tom Jul 9 '13 at 13:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.