When I use regex expressions to match parts of strings the matched results come back with curly braces around them, even though the sample string did not contain any curly braces. Take the following example in plpgsql:
CREATE OR REPLACE PROCEDURE somename()
LANGUAGE plpgsql
as $$
DECLARE
stage_x text;
testing text;
BEGIN
SELECT 'x' INTO stage_x;
RAISE NOTICE 'x is: %', stage_x; -- SHOWS ONLY x
regexp_matches(stage_x, '\w+') INTO regex_test;
RAISE NOTICE 'test is: %', regex_test; -- NOW IT SHOWS {x}
...
How do i avoid adding/remove the curly braces?
regexp_matches()
returns an array of all matches. The curly braces you see is only the default output formatting of array in Postgres. As you defined your variable as text
Postgres automatically converts the array to a string and that's why you get the curly braces.
You can define the variable as text[]
if you are interested in all matches.
If you are only interested in the first match, then just use that:
CREATE OR REPLACE PROCEDURE somename()
LANGUAGE plpgsql
as $$
DECLARE
stage_x text;
testing text;
BEGIN
stage_x := 'x';
RAISE NOTICE 'x is: %', stage_x; -- SHOWS ONLY x
regex_text := (regexp_matches(stage_x, '\w+'))[1]; --<< [1] picks the first array element
RAISE NOTICE 'test is: %', regex_test;
...
You shouldn't be using SELECT
to do simply assignments in PL/pgSQL, that's really very slow. Use :=
instead to assign a variable to the result of a single function call or value.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments