When you have a large table (16000 rows, 56 columns in my case)
----------------------------------------------------------------
CREATE TABLE table (
table_id int,
....
....
number_of_the_document int,
name text,
mothers_name text,
);
----------------------------------------------------------------
And you put an index on the atribute "number_of_the_document", for example.
----------------------------------------------------------------
CREATE INDEX idx_number_of_the_document ON table (number_of_the_document);
----------------------------------------------------------------
Then you want make this kind of select it willl use the
'idx_number_of_the_document' index.
----------------------------------------------------------------
SELECT name,mothers_name FROM table WHERE (number_of_the_document = 999999);
----------------------------------------------------------------
But if you want to put this into a function that will return a custom type:
----------------------------------------------------------------
CREATE TYPE names AS (name text,mothers_name text);
----------------------------------------------------------------
CREATE OR REPLACE FUNCTION function(int)
RETURNS names AS '
DECLARE
document ALIAS FOR $1;
answer names%ROWTYPE;
BEGIN
SELECT INTO answer name,mothers_name FROM table WHERE
(number_of_the_document = document);
RETURN answer;
END;
' LANGUAGE plpgsql;
----------------------------------------------------------------
Will that function uses the 'idx_number_of_the_document' index ??
The use of the "EXPLAIN ANALYZE SELECT * FROM function(XXXXXX)" didn't
help.
It said something "Function Scan on f1" , but what does it mean ?
