Gree Tree Python Gree Tree Python - 6 months ago 25
SQL Question

What Do $$ and :: do in Postgresql

I'm looking through a mess of (Postgresql) SQL code that I've inherited, and there are two pieces of notation that are being used that I don't follow. One is using

. E.g.
Create function ... returns FOO as $$
select NULL::int field_name from table

Having trouble googling because symbols, and I'm not even sure what I should be searching for.


expression::typename casts expression to type typename.

The dollar signs are used for literal string quoting. Function bodies are handed to the database as plain text strings. You could use plan old single quotes for this, but then you run potentially run into escaping issues inside your function body. So, often dollar sign quoting is used in this case.

Also note that dollar sign quoting does have a slight extension that looks like $somename$string text $$ goes here $somename$ - this allows us to use $$ itself inside such a string, along with potentially making the string designation a tiny bit more obvious. This is sometimes used in function definitions like create function x as $body$...$body$.