PgMDD: How to RETURNS TABLE

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)
AS
BEGIN
RETURN QUERY (SELECT id, name FROM customer);
END;

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
AS
$BODY$
   RETURN QUERY (SELECT id, name FROM customer);
$BODY$;
About these ads

Nerd.

Tagged with: , , , ,
Posted in MicroOLAP Database Designer for PostgreSQL
2 comments on “PgMDD: How to RETURNS TABLE
  1. David Fetter says:

    When will it support the ANSI syntax?

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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

Archives
Map of wandering
Follow

Get every new post delivered to your Inbox.

Join 78 other followers

%d bloggers like this: