SQL functions … are the better choice: For simple scalar queries. Not much to plan, better save any overhead. For single (or very few) calls per session. Nothing to gain from plan caching via prepared statements that PL/pgSQL has to offer. See below. If they are typically called in the context of bigger queries and … Read more
Either declare set1 and set2 as floats instead of integers or cast them to floats as part of the calculation: SET @weight= CAST(@set1 AS float) / CAST(@set2 AS float);
Use unnest. For example: CREATE OR REPLACE FUNCTION test( p_test text[] ) RETURNS void AS $BODY$ BEGIN SELECT id FROM unnest( p_test ) AS id; END; $BODY$ LANGUAGE plpgsql IMMUTABLE COST 1;
The command for listing all triggers is: show triggers; or you can access the INFORMATION_SCHEMA table directly by: select trigger_schema, trigger_name, action_statement from information_schema.triggers You can do this from version 5.0.10 onwards. More information about the TRIGGERS table is here.
Well, I believe it’s an “Old but Gold” question. The answer is: “It depends!”. The performances are such a delicate subject that it would be too much silly to say: “Never use subqueries, always join”. In the following links, you’ll find some basic best practices that I have found to be very helpful: Optimizing Subqueries … Read more
Postgres 12 or newer STORED generated columns are introduced with Postgres 12 – as defined in the SQL standard and implemented by some RDBMS including DB2, MySQL, and Oracle. Or the similar “computed columns” of SQL Server. Trivial example: CREATE TABLE tbl ( int1 int , int2 int , product bigint GENERATED ALWAYS AS (int1 … Read more
Search Tables: SELECT c.name AS ‘ColumnName’ ,(SCHEMA_NAME(t.schema_id) + ‘.’ + t.name) AS ‘TableName’ FROM sys.columns c JOIN sys.tables t ON c.object_id = t.object_id WHERE c.name LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName; Search Tables and Views: SELECT COLUMN_NAME AS ‘ColumnName’ ,TABLE_NAME AS ‘TableName’ FROM INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME LIKE ‘%MyName%’ ORDER BY TableName ,ColumnName;
The most general difference between procedures and functions is that they are invoked differently and for different purposes: A procedure does not return a value. Instead, it is invoked with a CALL statement to perform an operation such as modifying a table or processing retrieved records. A function is invoked within an expression and returns … Read more
Because the number can be up to 15 digits, you’ll need to cast to an 64 bit (8-byte) integer. Try this: SELECT * FROM table WHERE myint = mytext::int8 The :: cast operator is historical but convenient. Postgres also conforms to the SQL standard syntax myint = cast ( mytext as int8) If you have … Read more
I’m going to add a somewhat longer and more detailed explanation of the steps to take to solve this problem. I apologize if it’s too long. I’ll start out with the base you’ve given and use it to define a couple of terms that I’ll use for the rest of this post. This will be … Read more