How open as a table a postgres function?

Relativity Server
1

I have several postgres functions with params that return SETOF or SELECT:

CREATE OR REPLACE FUNCTION detail_product (ref_product TEXT)
RETURNS SETOF product
AS $$
BEGIN
RETURN QUERY SELECT * FROM product WHERE ref = ref_product;
END;
$$
LANGUAGE ‘plpgsql’;

When I run this from the sql editor:

SELECT * FROM “schema”.“detail_product”(:ref_product)

I get this error:

Error while refreshing data: Unsupported DataType Unknown

I can’t get a dataset if build this as a command - as command execute ok, but don’t get the data back-, so I suspect is necesary to add this as a datatable

I use DEVART/Postgres 9.1

P.D: When the option “Create NEW EXEC Statement” is executed, it add ‘"’ character around the param, and is wrong:

SELECT * FROM “superventas”.“detail_product”(:“ref_product”)

2

Hello,
You wrote:

Error while refreshing data: Unsupported DataType Unknown
Please try to set type for this parameter manually in schema.

P.D: When the option “Create NEW EXEC Statement” is executed, it add ‘"’ character around the param, and is wrong:
Can you provide your connection string?
What DA version do you use?

3

Set type for which parameter? And how?

This is the con string:

DEVART_POSTGRESQL.NET?Server=intersec.local;Database=BestSeller;UserID=postgres;Password=****;Protocol=2;

4
mamcx said:
5

OK, now I understand. In the dialog the datatype per default is datUnknown. Change it to the proper one make this work.

I can now create the fields but can’t figure how do this by code (I regenerate the schema by command line):

                    SchemaDataTable schemaTable = new SchemaDataTable { Name = procedure, IsPublic = true };

                    SchemaParameterCollection commandParameters = new SchemaParameterCollection();
                    connection.GetCommandParams(storedProcedure, ref commandParameters, true);
                    StringBuilder paramStr = new StringBuilder();

                    // Add all fields to the Schema Table definition
                    foreach (SchemaParameter parameter in commandParameters)
                    {
                        schemaTable.Parameters.Add(parameter);
                        paramStr.AppendFormat(":{0},", parameter.Name);
                    }
                    // Add AutoSQL statement
                    SchemaSQLStatement statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.SQL, TargetTable = procedure };
                    schemaTable.Statements.Clear();
                    //schemaTable.Statements.Add(statement);

                    statement.SQL = string.Format("SELECT * FROM {0} ({1})", procedure, paramStr.ToString().Substring(0, paramStr.Length-1));
                    // Load table fields info
                    SchemaFieldCollection tableFields = new SchemaFieldCollection();
                    connection.GetTableFields(statement.SQL, ref tableFields, true);
                    //connection.GetTableFields(
                    //SchemaField[] tableFields = schemaTable.Fields.GetServerRefreshedFields();
                    // Add all fields to the Schema Table definition
                    foreach (SchemaField field in tableFields)
                    {
                        if (field.DataType == DataType.Float)
                        {
                            field.DataType = DataType.Currency;
                        }

                        if (field.Name == "producttype")
                        {
                            field.DataType = DataType.Memo;
                        }
                        schemaTable.Fields.Add(field);
                    }
                    
                    foreach (SchemaField field in tableFields)
                        statement.ColumnMappings.Add(new SchemaColumnMapping(field.Name, field.Name, field.Name));
                    
                    schemaTable.Statements.Add(statement);

                    // Add created Schema Data Table to the Schema
                    lSchema.DataTables.Add(schemaTable);
6

Hello,
Please read the following issue. Hope it helps yo you.
http://connect.remobjects.com/discussion/721/is-possible-to-generate-schema-from-the-command-lineautomated

7

Yes, I ask about that issue, and that is what I’m doing. However, I don’t see how generate the dataset with params & fields. I only can get the fields or the params, not both.

8

Hello,
If you want to use stored procedure in DataTables use the code like this:

SchemaDataTable schemaTable = new SchemaDataTable { Name = "MyStoredProc", IsPublic = true };
 
String aSQL = "SELECT * FROM MyStoredProc(:ID)";
                
SchemaParameterCollection tableParameters = new SchemaParameterCollection();
connection.GetCommandParams(aSQL, ref tableParameters, false);
foreach (SchemaParameter parameter in tableParameters)
  schemaTable.Parameters.Add(parameter);

SchemaFieldCollection tableFields = new SchemaFieldCollection();
connection.GetTableFields(aSQL, ref tableFields, false);
foreach (SchemaField field in tableFields)
  schemaTable.Fields.Add(field);
 
SchemaSQLStatement statement = new SchemaSQLStatement { ConnectionType = connectionType, StatementType = StatementType.SQL, TargetTable = "MyStoredProc" };
foreach (SchemaField field in tableFields)
  statement.ColumnMappings.Add(new SchemaColumnMapping(field.Name, field.Name, field.Name));
 
schemaTable.Statements.Add(statement);
 
lSchema.DataTables.Add(schemaTable);