postgresql

Error when setting n_distinct using a plpgsql variable

This is by design. The manual explains in the chapter Variable Substitution: Variable substitution currently works only in SELECT, INSERT, UPDATE, and DELETE commands, because the main SQL engine allows query parameters only in these commands. To use a non-constant name or value in other statement types (generically called utility statements), you must construct the … Read more

How does PostgreSQL perform ORDER BY with a b-tree index on the field?

For a simple query like this Postgres will use an index scan and retrieve readily sorted tuples from the index in order. Due to its MVCC model Postgres had to always visit the “heap” (data pages) additionally to verify entries are actually visible to the current transaction. Quoting the Postgres Wiki on index-only scans: PostgreSQL … Read more

Return SETOF rows from PostgreSQL function

Sanitize function What you currently have can be simplified / sanitized to: CREATE OR REPLACE FUNCTION func_a (username text=””, databaseobject text=””) RETURNS ???? LANGUAGE plpgsql AS $func$ BEGIN RETURN QUERY EXECUTE format (‘SELECT * FROM %s v1 LEFT JOIN %I v2 USING (id)’ , CASE WHEN username=”*” THEN ‘view1’ ELSE ‘view3’ END , databaseobject); END … Read more

function returns multiple columns as a single column instead of multiple columns

you need to call the function like this: select * from foo(6); which will return something like this: project_id | project_name | project_type | project_description | project_status ———–|————–|————–|———————|—————- 6 | test project | inbound | inbound test | processing it’s a quirk of postgres that it can be called both ways and give you a … Read more

Postgres query optimization (forcing an index scan)

For testing purposes you can force the use of the index by “disabling” sequential scans – best in your current session only: SET enable_seqscan = OFF; Do not use this on a productive server. Details in the manual here. I quoted “disabling”, because you cannot actually disable sequential table scans. But any other available option … Read more