Re: [SQL] regexp_replace and UTF8
On 2009-01-30, Bart Degryse wrote:
>
> --=__Part8EA648F8.0__=
> Content-Type: text/plain; charset=UTF-8
> Content-Transfer-Encoding: quoted-printable
>
> 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)
that is not UTF8.
the UTF8 representation for 'ë' in SQL is e'\xC3\xAB' or chr(235)
your input appears to be encoded in LATIN-1 (or possibly 8859-13)
I think you'll need to write a function.
here, where I have database encoding UTF8 this appears to work as you
desire.
CREATE or replace FUNCTION htmlent(inp text) returns text as
$f$
DECLARE
str text;
BEGIN
str= regexp_replace(quote_literal( inp)
,$$&#(\d+);$$
,$$'||chr(\1)||'$$
,'g');
execute 'select '||str into str;
return str;
END
$f$
LANGUAGE PLPGSQL;
select htmlent('de patiënt niet');
probably the above should be expanded to handle named entities
like '&' too.
characters outside of the LATIN-1 space are not handled
but aparently this is what you want.
--
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
Well, aparenty my problem remains, because the boss want that the
programmers just need to call
select * from report('table_name'). Following your previous sample
function, im forced to use it in the form
select * from report('table_name') as x(a int, b varchar, c int), and that
"as x(...)" is the kind of thing hes triyng to avoid. Same feeling about
fetching records at application level. To bad for me, im affraid :(
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:
> Well, aparenty my problem remains, because the boss want that the
> programmers just need to call
> select * from report('table_name').
Then, AFAIK, you're not going to have much luck, as Pg needs to know the
columns that'll be output before the function is called. Applications
and DB access interfaces also usually also need to know the column list
beforehand.
If you return a refcursor you can at least use:
SELECT report('table_name');
FETCH ALL IN 'cursorname';
The documentation has a more full example:
http://www.postgresql.org/docs/8.2/interactive/plpgsql-cursors.html#AEN40465
Personally, I find it difficult to imagine what could be wrong with that.
--
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
