Uploaded image for project: 'Teiid Designer'
  1. Teiid Designer
  2. TEIIDDES-2213

JDBC Importer with PostgreSQL function that returns table imports parameters incorrectly

    XMLWordPrintable

Details

    • Bug
    • Resolution: Done
    • Major
    • 9.0, 8.6.1, 9.0.1
    • 8.3.2
    • Import/Export
    • None
    • Hide

      Create the table and 2 functions in a PostgreSQL database:

      CREATE TABLE foo (fooid int, foosubid int, fooname text);
      NSERT INTO foo (fooid,foosubid,fooname)values (1,1,"ONE");
      INSERT INTO foo (fooid,foosubid,fooname)values (2,2,"TWO");
      CREATE OR REPLACE FUNCTION extended_foo(pfooid int)
      RETURNS TABLE(fooid int, foosubid int, fooname text) AS $$
      BEGIN
      RETURN QUERY SELECT * FROM foo WHERE foo.fooid = pfooid;
      END;
      $$ LANGUAGE plpgsql;
      CREATE OR REPLACE FUNCTION getfoo(int) RETURNS setof foo AS '
      SELECT * FROM foo WHERE fooid = $1;
      ' LANGUAGE SQL;

      2. Run the JDBC importer on your Postgres database selecting both functions getfoo and extended_foo.
      3. Notice the getfoo function (which was created with RETURNS setof) imports correctly with 1 input parameter and a Resultset with 3 columns.
      4. But the extended_foo function (which was created with RETURNS TABLE) imports with 4 input parameters and no resultset. This one should import the same as the getfoo function.

      Show
      Create the table and 2 functions in a PostgreSQL database: CREATE TABLE foo (fooid int, foosubid int, fooname text); NSERT INTO foo (fooid,foosubid,fooname)values (1,1,"ONE"); INSERT INTO foo (fooid,foosubid,fooname)values (2,2,"TWO"); CREATE OR REPLACE FUNCTION extended_foo(pfooid int) RETURNS TABLE(fooid int, foosubid int, fooname text) AS $$ BEGIN RETURN QUERY SELECT * FROM foo WHERE foo.fooid = pfooid; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION getfoo(int) RETURNS setof foo AS ' SELECT * FROM foo WHERE fooid = $1; ' LANGUAGE SQL; 2. Run the JDBC importer on your Postgres database selecting both functions getfoo and extended_foo. 3. Notice the getfoo function (which was created with RETURNS setof) imports correctly with 1 input parameter and a Resultset with 3 columns. 4. But the extended_foo function (which was created with RETURNS TABLE) imports with 4 input parameters and no resultset. This one should import the same as the getfoo function.
    • Workaround Exists
    • Hide

      Manually model the Function instead of importing.

      Show
      Manually model the Function instead of importing.
    • Hide
      Known Issue:
      - For PostgreSQL drivers prior to 9.3, a bug exists for import of the described procedures. For pre-9.3 drivers, the resultset params are erroneously all returned as IN parameters. The workaround is to manually edit the imported procedure for pre-9.3 driver import
      Show
      Known Issue: - For PostgreSQL drivers prior to 9.3, a bug exists for import of the described procedures. For pre-9.3 drivers, the resultset params are erroneously all returned as IN parameters. The workaround is to manually edit the imported procedure for pre-9.3 driver import
    • Documented as Known Issue

    Description

      Using the JDBC Importer on a PostgreSQL Function that RETURNS TABLE the columns that are returned when running the function are imported as input parameters not as resultset.

      Attachments

        Activity

          People

            mdrillin1@redhat.com Mark Drilling (Inactive)
            rhn-support-dsteigner Deborah Steigner (Inactive)
            Votes:
            0 Vote for this issue
            Watchers:
            6 Start watching this issue

            Dates

              Created:
              Updated:
              Resolved: