Re: returning a recordset from PLpg/SQL - Mailing list pgsql-sql

Tom Lane wrote:
 > Terence Kearns <terencek@isd.canberra.edu.au> writes:
 >
 >>I tried
 >>RETURNS SETOF RECORD
 >>but that doesn't work
 >
 >
 > Sure it does, if you use it correctly.  Better show us what you did.
 >
 >             regards, tom lane

Well I haven't yet done anything because I couldn't get anything to
compile which returned SETOF RECORD..

I'll write some code which I would like to work.

Here's the relevent part of the schema:
(see attached gif for full diagram)

CREATE DOMAIN docs.context
   AS varchar(32)
   NOT NULL;

CREATE TABLE docs.documents
(
   doc_id int4 NOT NULL DEFAULT nextval('docs.seq_docs_doc_id'::text),
   doc_title varchar(256) NOT NULL,
   doc_summary varchar(512),
   doc_folder_id int4 NOT NULL,
   doc_sort_index int4,
   CONSTRAINT pkey_docs PRIMARY KEY (doc_id),
   CONSTRAINT fkey_documents_folder_id FOREIGN KEY (doc_folder_id)
REFERENCES docs.folders (folder_id) ON UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT unique_doc_id_folder_id UNIQUE (doc_id, doc_folder_id)
) WITHOUT OIDS;
COMMENT ON TABLE docs.documents IS 'This table records a LOGICAL
document (as opposed to it\'s physical incarnation). Metadata plus extra
attribute_values are recorded against this entity. Details regarding
physical manifestations of these documents are contained in the
document_instance table which might contain versions of the same
document, but in different formats or in different locations (mirrors).
Versioning information is also kept as a field in the document instance
table (and not in a dedicated document version table for reasons of
performance and simplicity).';

CREATE TABLE docs.attribute_profiles
(
   att_profile_id int4 NOT NULL DEFAULT
nextval('docs.seq_attribute_profiles_att_profile'::text),
   att_profile_title varchar(128) NOT NULL,
   att_profile_description text,
   att_profile_context docs.context,
   CONSTRAINT pkey_attribute_profiles PRIMARY KEY (att_profile_id),
   CONSTRAINT fkey_att_profile_context FOREIGN KEY (att_profile_context)
REFERENCES docs.contexts (context) ON UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT unique_att_profile_title_context UNIQUE
(att_profile_title, att_profile_context)
) WITHOUT OIDS;
COMMENT ON TABLE docs.attribute_profiles IS 'This table keeps a list of
entity attribute collections. This list is divided into application
contexts. The application can then select from a list of profiles in
it\'s own context and use  each profile to build the data entry form.
Form building is the main usage of this table. Insert and lookup of user
data  does not require this table. The attribute_value table(s) is used
for that.';

CREATE TABLE docs.entity_attributes
(
   att_id int4 NOT NULL DEFAULT
nextval('docs.seq_entity_attribute_id'::text),
   att_profile_id int4 NOT NULL,
   att_name varchar(128) NOT NULL,
   att_sort_index int4,
   att_data_type varchar(32) NOT NULL,
   att_is_metadata bool NOT NULL DEFAULT false,
   CONSTRAINT pkey_entity_attributes PRIMARY KEY (att_id),
   CONSTRAINT fkey_entity_attribute_profile_id FOREIGN KEY
(att_profile_id) REFERENCES docs.attribute_profiles (att_profile_id) ON
UPDATE RESTRICT ON DELETE RESTRICT,
   CONSTRAINT unique_att_name_profile_id UNIQUE (att_name, att_profile_id),
   CONSTRAINT check_entity_att_data_type CHECK att_data_type::text =
'timestamp'::text OR att_data_type::text = 'varchar'::text OR
att_data_type::text = 'text'::text OR att_data_type::text = 'integer'::text
) WITHOUT OIDS;
COMMENT ON TABLE docs.entity_attributes IS 'This table contains a list
of user-defined (developer defined) entity fields/attributes. It is used
to facilitate document entity exstensibility at runtime. In other words,
you can add fields to the document information form without ALTERing the
physical data structure of this schema. Corresponding user-data is not
stored here, but in the attribute_value table(s). IMPORTANT: see
comments on the att_data_type field.';

CREATE TABLE docs.document_attribute_values
(
   entity_att_id int4 NOT NULL,
   doc_id int4 NOT NULL,
   value_timestamp timestamp,
   value_varchar varchar(256),
   value_text text,
   value_integer int4,
   CONSTRAINT pkey_document_attribute_values PRIMARY KEY (entity_att_id,
doc_id),
   CONSTRAINT fkey_document_attribute_values_att_id FOREIGN KEY
(entity_att_id) REFERENCES docs.entity_attributes (att_id) ON UPDATE
RESTRICT ON DELETE RESTRICT,
   CONSTRAINT fkey_document_attribute_values_doc_id FOREIGN KEY (doc_id)
REFERENCES docs.documents (doc_id) ON UPDATE RESTRICT ON DELETE RESTRICT
) WITHOUT OIDS;
COMMENT ON TABLE docs.document_attribute_values IS 'This table contains
user entries for document entity_attributes. The att_data_type field
contained in the entity_attributes table identified by value_att_id
points to the actual column in this table which ultimately stores the
target data. In other words, apart from the entity_att_id and the doc_id
field, only one out of the other fields is ever populated.';


So here's the desired function:
(of course, there's probably a whole bunch of reasons why this sketched
code won't work, I just want to know if the same outcome is at all
possible).

CREATE FUNCTION "docs"."details_for_profile" (docs.context, VARCHAR)
RETURNS SETOF "RECORD" AS'
DECLARE
        profile_context ALIAS FOR $1;
        profile_title ALIAS FOR $2;
        result_row record;
        lookup_row record;
        str_temp text;
        int_temp int4;
BEGIN
      str_temp := '''';
      FOR lookup_row IN SELECT att.*
      FROM attribute_profiles prf, entity_attributes att
      WHERE prf.att_profile_context = profile_context
            AND prf.att_profile_title = profile_title
            AND prf.att_profile_id = att.att_profile_id
      LOOP
           str_temp := str_temp
                    || lookup_row.att_name
                    || lookup_row.att_data_type'','' ;
      END LOOP;
      EXECUTE ''CREATE TYPE tmp_type (''
          || ''doc_id int4,''
          || ''doc_title varchar(256),''
          || ''doc_summary varchar(512),''
          || ''folder_id int4,''
          || ''sort_index int4,''
          || str_temp
          || ''profile_id int4''
      '')'';

      result_row tmp_type;

      int_temp := 0;

      FOR lookup_row IN SELECT d.*, v.*, att.att_name, att.att_data_type
      FROM documents d, document_attribute_values v, entity_attributes att
      WHERE d.doc_id = v.doc_id AND v.entity_att_id = att.att_id
      ORDER BY d.doc_id
      LOOP
          IF int_temp != lookup_row.doc_id THEN
             IF int_temp !=0 THEN
                RETURN NEXT result_row;
             END IF;
             int_temp := lookup_row.doc_id;
             result_row.doc_id := lookup_row.doc_id;
             result_row.doc_title := lookup_row.doc_title;
             result_row.doc_summary := lookup_row.doc_summary;
             result_row.folder_id := lookup_row.folder_id;
             result_row.doc_id := lookup_row.doc_id;
             result_row.profile_id = lookup_row.profile_id;
          END IF;
          EXECUTE ''result_row.'' || lookup_row.att_name
                  || '' := v.value_'' || lookup_row.att_data_type;
      END;
      RETURN NEXT result_row;
      RETURN;
END;
'LANGUAGE 'plpgsql' STABLE RETURNS NULL ON NULL INPUT SECURITY INVOKER;