Adding a crucial element to an example

The examples in the documentation are the most valuable part.

In this section, you create the table for the example, but you do not
populate it, although the example select statements are against the
phantom population that has not been inserted.

I suggest strongly including the minimal code necessary to populate
the table, so that the user doesn't have to populate it himself. Call
me lazy, but I did go into pgAdmin and insert values after creating a
primary key.

Cutting and pasting examples and then playing around with them is by
far, by far the most productive way to learn the system.

John

35.4.2. SQL Functions on Composite Types
When writing functions with arguments of composite types, we must not
only specify which argument we want (as we did above with $1 and $2)
but also the desired attribute (field) of that argument. For example,
suppose that emp is a table containing employee data, and therefore
also the name of the composite type of each row of the table. Here is
a function double_salary that computes what someone's salary would be
if it were doubled:

CREATE TABLE emp ( name text, salary numeric, age integer, cubicle
point );

CREATE FUNCTION double_salary(emp) RETURNS numeric AS $$
SELECT $1.salary * 2 AS salary;
$$ LANGUAGE SQL;

SELECT name, double_salary(emp.*) AS dream FROM emp WHERE emp.cubicle
~= point '(2,1)';

name | dream
------+-------
Bill | 8400

On fre, 2010-07-23 at 12:02 +0200, John Gage wrote:
> In this section, you create the table for the example, but you do
> not
> populate it, although the example select statements are against the
> phantom population that has not been inserted.
>
> I suggest strongly including the minimal code necessary to populate
> the table, so that the user doesn't have to populate it himself.
> Call
> me lazy, but I did go into pgAdmin and insert values after creating
> a
> primary key.

Could you send a patch, or a list of the statements that need to be
inserted?

After the CREATE statement I would simply put:

INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

What that accomplishes is making it so the user can simply copy and
paste the entire section and run it in pg_admin. Then, the user can
start fiddling with it as he wants to.

I know this sounds terribly simple, but simplicity is good,
particularly when you're climbing a steep learning curve. And there
is certainly, unequivocally, no harm in simplicity.

Thanks,

John

On Jul 24, 2010, at 2:21 PM, Peter Eisentraut wrote:

> On fre, 2010-07-23 at 12:02 +0200, John Gage wrote:
>> In this section, you create the table for the example, but you do
>> not
>> populate it, although the example select statements are against the
>> phantom population that has not been inserted.
>>
>> I suggest strongly including the minimal code necessary to populate
>> the table, so that the user doesn't have to populate it himself.
>> Call
>> me lazy, but I did go into pgAdmin and insert values after creating
>> a
>> primary key.
>
> Could you send a patch, or a list of the statements that need to be
> inserted?
>
>
> --
> Sent via pgsql-docs mailing list (pgsql-docs(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-docs

On lör, 2010-07-24 at 16:26 +0200, John Gage wrote:
> After the CREATE statement I would simply put:
>
> INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');

done

In 34.4.3. SQL Functions with Output Parameters we have:

CREATE FUNCTION sum_n_product (x int, y int, OUT sum int, OUT product
int) AS
'SELECT $1 + $2, $1 * $2'
LANGUAGE SQL;

SELECT * FROM sum_n_product(11,42);

sum | product
-----+---------
53 | 462
(1 row)

Then in 34.4.7. SQL Functions Returning Sets we have (without
an example SELECT statement):

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT
product int) RETURNS SETOF record AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

The problem is that if the reader copies and pastes the last function
definition and then attempts to run it, quite obviously he gets an
error because there is no table "tab".
I would add a table definition and fill it with dummy data and then
give two example SELECT statements after the function definition:

CREATE TABLE tab ( y integer, z integer );
INSERT INTO tab VALUES (1, 2), (3,4), (5,6), (7,8);

CREATE FUNCTION sum_n_product_with_tab (x int, OUT sum int, OUT
product int) RETURNS SETOF record AS $$
SELECT $1 + tab.y, $1 * tab.y FROM tab;
$$ LANGUAGE SQL;

SELECT sum_n_product_with_tab(10);
SELECT * from sum_n_product_with_tab(10);

I think one of the things this presentation accomplishes is to
emphasize that a table definition is really not much more than a type
definition or object definition or what you will. That is not said
derogatorily at all. Rather, I believe it emphasizes the
extraordinary power of Postgresql.

John

On sön, 2010-07-25 at 11:34 +0200, John Gage wrote:
> I would add a table definition and fill it with dummy data and then
> give two example SELECT statements after the function definition:

Done

John Gage wrote:
> After the CREATE statement I would simply put:
>
> INSERT INTO emp VALUES ('Bill', 4200, 45, '(2,1)');
>
> What that accomplishes is making it so the user can simply copy and
> paste the entire section and run it in pg_admin. Then, the user can
> start fiddling with it as he wants to.
>
> I know this sounds terribly simple, but simplicity is good,
> particularly when you're climbing a steep learning curve. And there
> is certainly, unequivocally, no harm in simplicity.

Our documentation got this good only through many small improvements
like this. :-)

--
Bruce Momjian <bruce(at)momjian(dot)us> http://momjian.us
EnterpriseDB http://enterprisedb.com

+ It's impossible for everything to be true. +