[SQL] regexp_replace and UTF8
Hi,
I have a text field with data like this: 'de patiënt niet'
(without the quotes).
I would like to convert this string to look like this: 'de patiënt
niet'
Basically what I need to do (I think) is
- get rid of the &, # and ;
- convert the number to hex
- make a UTF8 from that (thus: \xEB)
- convert that to SQL_ASCII
Since this works:
select regexp_replace('de patiënt niet', '&#(\\d+);' (
file://d+);'/ ), '\xEB', 'g')
I was thinking that this would work too, but it doesn't
select regexp_replace('de patiënt niet', '&#(\\d+);' (
file://\d+);' ), '\x' || to_hex(E'\\1'), 'g')
It gives me:
ERROR: function to_hex("unknown") is not unique
HINT: Could not choose a best candidate function. You may need to
add explicit type casts.
So I changed it to
select regexp_replace('de patiënt niet', '&#(\\d+);' (
file://\d+);' ), '\x' || to_hex(CAST (to_number(E'\\1','999') AS
INTEGER)), 'g')
which kind of works except that the result is not what I need:
'de patix1nt niet'
Can anyone help me fix this or point me to a better approach.
By the way, changing the way data is put into the field is
unfortunately not an option.
Many thanks in advance.
ICT Departement - Indicator NV
Bart Degryse
Re: [SQL] regexp_replace and UTF8
"Bart Degryse" writes: > Hi, > I have a text field with data like this: 'de patiënt niet' > Can anyone help me fix this or point me to a better approach. > By the way, changing the way data is put into the field is > unfortunately not an option. > Many thanks in advance. You could use a plperl function to use one of the many html parsing perl modules? -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Ask me about EnterpriseDB's 24x7 Postgres support! -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] dynamic OUT parameters?
Hi all. I have a situation here: I have to make a 'report' for a table.
I'll try to explain the functionality needed:
Lets say i have a table like this
\d people
name varchar
id_city integer (references city(id_city))
\d city
id_city integer
city_name varchar
id_country (references country (id_country))
\d country
id_country integer
country_name varchar
So, if i call report('people') i should return
name|city |country
Philip |Portland |UEA
Manuel |Madrid |España
And so on. This is: "Do *not* include those fields who are FK, instead,
put the referenced keyword from the referenced table".
It is not hard to do this in a query. Is not neither hard to make it a
function. But i have many tables to 'report', and the application leader
does not want querys in the program, just procedures call. So i would
like to have a generic function who should have the logic of 'selecting
the columns to show'. And then i have to ask:
1) There is a way to make a function returning "any amount of any type
of arguments"?
2) Can i make a special type "on_the_fly" and returning setof "that_type"?
3) Should (and/or can) i make a view "on_the_fly" and return from
"that_view"?
Comments awaiting. I hope the explanation is clear enough.
Thanks!
Gerardo
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] regexp_replace and UTF8
In article <87ljstm4eq@oxford.xeocode.com>, Gregory Stark writes: > "Bart Degryse" writes: >> Hi, >> I have a text field with data like this: 'de patiënt niet' >> Can anyone help me fix this or point me to a better approach. >> By the way, changing the way data is put into the field is >> unfortunately not an option. > You could use a plperl function to use one of the many html parsing perl > modules? Yes, either plperl or some external HTML tool. >> Basically what I need to do (I think) is >> - get rid of the &, # and ; >> - convert the number to hex >> - make a UTF8 from that (thus: \xEB) >> - convert that to SQL_ASCII You know that SQL_ASCII is a misnomer for "no encoding at all, and I don't care"? I'd use UTF8 or (if you stay in Western Europe) Latin9. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
Gerardo Herzig wrote: > 1) There is a way to make a function returning "any amount of any type > of arguments"? RETURNS SETOF RECORD The application must, however, know what columns will be output by the function ahead of time and call it using an explicit column declaration list. For example, the following function returns a table of width `_ncols' columns repeated over `_ncols' records: CREATE OR REPLACE FUNCTION dyncol(_ncols INTEGER) RETURNS SETOF RECORD AS $$ DECLARE _out RECORD; _stm text; _i integer; BEGIN _stm = 'SELECT 1'; FOR _i IN 2.._ncols LOOP _stm = _stm || ', ' || _i; END LOOP; _stm = _stm || ' FROM generate_series(1,' || _ncols || ');' ; FOR _out IN EXECUTE _stm LOOP RETURN NEXT _out; END LOOP; END; $$ LANGUAGE 'plpgsql'; Because Pg must know what the return columns will be before the function is called, you can't just call it as `dyncol(4)' : test=> SELECT dyncol(4); ERROR: set-valued function called in context that cannot accept a set CONTEXT: PL/pgSQL function "dyncol" line 12 at RETURN NEXT you must instead specify a table alias with a column definition, eg: test=> SELECT * FROM dyncol(4) AS x(a INTEGER, b INTEGER, c INTEGER, d INTEGER); a | b | c | d ---+---+---+--- 1 | 2 | 3 | 4 1 | 2 | 3 | 4 1 | 2 | 3 | 4 1 | 2 | 3 | 4 (4 rows) Of course, nothing stops you from writing another function that provides this information to the application, so it can call the first function to get the information required to correctly call your dynamic reporting function. > 2) Can i make a special type "on_the_fly" and returning setof "that_type"? You're better off using SETOF RECORD, at least in my opinion. -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
Craig Ringer writes: >> 2) Can i make a special type "on_the_fly" and returning setof "that_type"? > You're better off using SETOF RECORD, at least in my opinion. Another possibility is to return a cursor. regards, tom lane -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
> Gerardo Herzig wrote: > >> 1) There is a way to make a function returning "any amount of any type >> of arguments"? > > RETURNS SETOF RECORD > > The application must, however, know what columns will be output by the > function ahead of time and call it using an explicit column declaration > list. For example, the following function returns a table of width > `_ncols' columns repeated over `_ncols' records: > > CREATE OR REPLACE FUNCTION dyncol(_ncols INTEGER) RETURNS SETOF RECORD > AS > $$ > DECLARE > _out RECORD; > _stm text; > _i integer; > BEGIN > _stm = 'SELECT 1'; > FOR _i IN 2.._ncols LOOP > _stm = _stm || ', ' || _i; > END LOOP; > _stm = _stm || ' FROM generate_series(1,' || _ncols || ');' ; > FOR _out IN EXECUTE _stm LOOP > RETURN NEXT _out; > END LOOP; > END; > $$ LANGUAGE 'plpgsql'; > > > Because Pg must know what the return columns will be before the function > is called, you can't just call it as `dyncol(4)' : > > test=> SELECT dyncol(4); > ERROR: set-valued function called in context that cannot accept a set > CONTEXT: PL/pgSQL function "dyncol" line 12 at RETURN NEXT > > you must instead specify a table alias with a column definition, eg: > > test=> SELECT * FROM dyncol(4) AS x(a INTEGER, b INTEGER, c INTEGER, d > INTEGER); > a | b | c | d > ---+---+---+--- > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > 1 | 2 | 3 | 4 > (4 rows) > > > Of course, nothing stops you from writing another function that provides > this information to the application, so it can call the first function > to get the information required to correctly call your dynamic reporting > function. > >> 2) Can i make a special type "on_the_fly" and returning setof >> "that_type"? > > You're better off using SETOF RECORD, at least in my opinion. > > -- > Craig Ringer Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD forces the use of OUT parameters. I will give your idea a try. Thanks Craig! Gerardo -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
Re: [SQL] dynamic OUT parameters?
gher...@fmed.uba.ar wrote: > Oh, that looks promising. I wrongly supposed that RETURNING SETOF RECORD > forces the use of OUT parameters. I will give your idea a try. Tom Lane's point about using a refcursor is (unsurprisingly) a good one. If you return a refcursor from your function, you don't have to do any special work to call the function, and you can (with most DB access APIs) FETCH records from the cursor rather conveniently. See: http://www.postgresql.org/docs/8.3/static/plpgsql-cursors.html -- Craig Ringer -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
