Menu
I am trying to do something like this:
select char_length(select text_field from table where key = 1)
This won't work, and I presume, because the return type of a query is a table, not a string or text variable.
Is there a way to specify row,col of the result from a select statement?
edit: I overlooked to mention, that char_length is a function.
215
In PostgreSQL, the select into statement to select data from the database and assign it to a variable. Syntax: select select_list into variable_name from table_expression; In this syntax, one can place the variable after the into keyword.
The normal syntax to call another PL/pgSQL function from within PL/pgSQL is to either reference the function in a SQL SELECT statement, or during the assignment of a variable. For example: SELECT function_identifier ( arguments ); variable_identifier := function_identifier ( arguments );
It can be used to replace single quotes enclosing string literals (constants) anywhere in SQL scripts. The body of a function happens to be such a string literal. Dollar-quoting is a PostgreSQL-specific substitute for single quotes to avoid escaping of nested single quotes (recursively).
PostgreSQL UsageThe PostgreSQL EXECUTE command prepares and runs commands dynamically. The EXECUTE command can also run DDL statements and retrieve data using SQL commands. Similar to SQL Server, you can use the PostgreSQL EXECUTE command with bind variables.
select char_length(text_field) from "table" where key = 1
When passing the result of a query to a function, simply wrap the query in brackets:
select char_length((select text_field from table where key = 1));
The outer set of brackets is for the function, the inner set converts a query to a result.
This syntax is not specific to postgres - it applies to all SQL servers.
This link shows the above code executing correctly (thanks to @Fahim Parkar for this)
Although, you could re-factor your query to not require this syntax, nevertheless this is how you "pass the result of a query to a function".
76
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
