Menu
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 $$ and select NULL::int field_name from table.
Having trouble googling because symbols, and I'm not even sure what I should be searching for.
810
The CAST() syntax conforms to SQL. The type 'string' syntax is a generalization of the standard: SQL specifies this syntax only for a few data types, but PostgreSQL allows it for all types. The syntax with :: is historical PostgreSQL usage, as is the function-call syntax.
PostgreSQL supports a character data type called TEXT. This data type is used to store character of unlimited length.
In PostgreSQL, the dollar-quoted string constants ($$) is used in user-defined functions and stored procedures. In PostgreSQL, you use single quotes for a string constant like this: select 'String constant'; When a string constant contains a single quote ('), you need to escape it by doubling up the single quote.
PostgreSQL provides two native operators -> and ->> to help you query JSON data. The operator -> returns JSON object field as JSON. The operator ->> returns JSON object field as text.
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$.
123
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
