Our client asked:

I have a script that can be created directly from PgAdmin, but it is not possible to create these scripts in PgMDD.

The function has a simple structure as defined below. The important part, is the return type (TABLE)

CREATE FUNCTION function_name() RETURNS TABLE(column_name_1 integer, column_name_2 character varying)
RETURN QUERY (SELECT id, name FROM customer);

PgMDD does not allow you to to that.

Yeap, indeed PgMDD doesn’t support ANSI syntax RETURNS TABLE, but you may do this in PostgreSQL way by using OUT parameters.

There is a good article “Using RETURNS TABLE vs. OUT parameters” by Leo Hsu and Regina Obe describing this approach.

So this function may be defined like this using PgMDD:

CREATE FUNCTION function_name(
  OUT column_name_1 integer,  
  OUT column_name_2 character varyingRETURNS SETOF RECORD
   RETURN QUERY (SELECT id, name FROM customer);

2 thoughts on “PgMDD: How to RETURNS TABLE

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s