For questions relating to "dollar quoting" with PostgreSQL. Use this tag in combination with the general [postgresql] tag. If your question applies to PostgreSQL more generally, use the [postgresql] tag only.
Questions tagged [dollar-quoting]
15 questions
146
votes
2 answers
What are '$$' used for in PL/pgSQL
Being completely new to PL/pgSQL , what is the meaning of double dollar signs in this function:
CREATE OR REPLACE FUNCTION check_phone_number(text)
RETURNS boolean AS $$
BEGIN
IF NOT $1 ~ e'^\\+\\d{3}\\ \\d{3} \\d{3} \\d{3}$' THEN
RAISE…
vector
- 7,334
- 8
- 52
- 80
6
votes
4 answers
Unterminated dollar-quoted string at or near "$$
I'm trying to declare some variables using DBeaver and keep hitting this error.
Unterminated dollar-quoted string at or near "$$
DO $$
DECLARE A integer; B integer;
BEGIN
END$$;
Any ideas?
user1158745
- 2,402
- 9
- 41
- 60
2
votes
1 answer
How to use Postgres regular expression matching on a string with $ dollar signs?
I'm using the ~* operator in a SELECT statement to search for parts of a string.
Where I'm having trouble is with a particular string that starts/ends with the $ dollars sign.
SELECT "artists".* FROM "artists" WHERE (name ~* '$uicideBoy$')
If I…
Shpigford
- 24,748
- 58
- 163
- 252
1
vote
1 answer
CREATE FUNCTION statement in an IF block throws error, while running on its own works
I'm new to PostgreSQL (currently on PostgreSQL 13) and may be confusing things from what other SQL encounters I've had (Microsoft SQL).
The goal is to assert there are no values in a table column which would get truncated, then reduce the column…
mp_in_training
- 37
- 7
1
vote
1 answer
What does $$ mean when using DO, CREATE FUNCTION, etc?
I have seen the double dollar sign ($$) being used often in FUNCTION declarations and while doing declare statements. I acknowledge that it is probably some syntax related feature, but I am not too clear how/why to use it and when.
Code examples of…
PoorProgrammer
- 459
- 1
- 4
- 14
0
votes
2 answers
String concatenation with dollar-quoting
I use concatenation to prepare a query string with parameter values
When I use single quotes:
p_ReqStr_old := '
with prm as
(
select
1::int4 as id, ' || '
to_timestamp(''' || to_char(p_BegRepDate, 'DD.MM.YYYY') ||…
Ivan Vodopyanov
- 119
- 1
- 1
- 7
0
votes
1 answer
POSTGRESQL Dollar Quotes in Where Clause
For people who tried or needed a solution to escape every special character (even $) in a WHERE CLAUSE in POSTGRESQL, here is how it should be use
the documentation can be somehow hard to understand, and there is no proper example of it so here is…
Valentin
- 56
- 5
0
votes
1 answer
PostgreSQL: Cannot use numbers as tags for dollar quoted strings
How do we use numbers as tags for dollar quoted strings?
INSERT INTO table(user_id,user_data)
values (22176,to_jsonb($123${"name": "Helo. $ what is this $"}$123$::jsonb))
The above query fails, however if I replace numeric tags with alphabetic…
relentless-coder
- 1,478
- 3
- 20
- 39
0
votes
1 answer
Postgres error with SQL state function: 42601
create or replace function fibonacci(int) returns int
as $$
declare
a int;
res int := 0;
begin
a := $1;
if(a = 1 or a = 2) then
res :=1;
else
res := fibonacci(a - 2) + fibonacci(a - 1);
end if;
return…
0
votes
1 answer
Is dollar-quoting in Postgres enough to escape malicious inputs?
Is dollar quoting enough to prevent malicious inputs like SQL injection?
For example:
SELECT * FROM mytable WHERE title = $secret$ hack'-- $secret$
where user input is
hack'--
Noob Life
- 540
- 3
- 10
0
votes
1 answer
DBeaver not compiling a function that compiles using psql client
I can compile the next function using psql client. However, DBeaver-ce returns the error
SQL Error [42601]: Unterminated dollar quote started at position 0 in SQL $$;. Expected terminating $$".
Why?
CREATE OR REPLACE FUNCTION…
Jose Cabrera Zuniga
- 2,348
- 3
- 31
- 56
0
votes
1 answer
Why I have DOLLAR QUOTE error in console when i run tests and not when i run the function on the workbench?
I have a problem with my postgres function.
I' ve created a function to copy data from db to csv.
I' ve tried to run the function on the workbench and there aren't errors, but when i run tests i have dollar quoting error.
This is the…
Michele Verriello
- 123
- 2
- 15
0
votes
0 answers
Why do I have dollar-quote error on function with copy on postgres
I have a problem with a Postgres function with a COPY TO A CSV FILE, I have dollar-quote error
This is the function
CREATE OR REPLACE FUNCTION save_audit_deletions(IN table_name text)
RETURNS void AS
$body$
DECLARE
interval…
Michele Verriello
- 123
- 2
- 15
-1
votes
1 answer
Syntax error when trying execute dynamic query which contain dollar quoting to escape nested single quotes
I have following piece of code, which does following
Generate set of string column names when provided a date range which are simply dates between the range
Then this function is used within an another function to generate dynamic query string
Next…
user158
- 12,852
- 7
- 62
- 94
-1
votes
1 answer
What is `AS $$` in sql functions?
CREATE FUNCTION one() RETURNS integer AS $$
SELECT 1 AS result;
$$ LANGUAGE SQL;
why i cant write like this:
CREATE FUNCTION one() RETURNS integer
SELECT 1 AS result;
what is AS $$?
what is $$ LANGUAGE SQL?
what is $$?
Алексей Соснин
- 1,513
- 7
- 12