Thread: functions which return tuples

Hello,

Is it possible write stored procedure (sorry, function) which will
return tuples one by one. Like:
    select * from my_func(1,2,3);
    1 Ivan
    2 Petr
    3 Irzi
etc.
Like in, for example, Interbase (this is completely unnatural proc):
create procedure find_something_for(p integer, w integer)
returns (k integer, t varchar(30) )
as
begin

for select n,v from t001 into :k,:t do
begin
  if(n>10) then suspend;
end

end

'suspend' will return result to caller and then execution will continue
from saved position.

This is needed when computations is much harder than comparision n and 10

--
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga",
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

Igor Roboul <igor@raduga.dyndns.org> writes:
> Is it possible write stored procedure (sorry, function) which will
> return tuples one by one. Like:
>     select * from my_func(1,2,3);

Doesn't work at the moment, but there's been talk of making it work
for 7.2.  See my much longer post about functions returning tuples,
a few days back (either here or in pgsql-sql, I forget...).  The
executive summary: it's kind of a mess at the moment.

            regards, tom lane

Hello,

Looking at the docs for pgsql I have only found stuff on altering a table
for default and renaming a column but nothing on changing the size. I want
to increase the size of a field from 2048 to 4096.

What is the syntax for this?

Thanks,
J

On Wed, Nov 01, 2000 at 12:43:45PM -0500, J. Atwood wrote:
> Hello,
>
> Looking at the docs for pgsql I have only found stuff on altering a table
> for default and renaming a column but nothing on changing the size. I want
> to increase the size of a field from 2048 to 4096.
Can you tell me, how will do this for example with Oracle? Or Interbase?
Just because I had needed this, I did:
1) create new attribute (ok, column)
2) run "update table1 set n_col=o_col"
3) drop old column (ok, attribute)

--
Igor Roboul, Unix System Administrator & Programmer @ sanatorium "Raduga",
Sochi, Russia
http://www.brainbench.com/transcript.jsp?pid=304744

This requires you to change NAMEDATALEN in src/include/postgres_ext.h.

Note this requires a recompile, initdb, createdb, etc.

Also note that databases with different NAMEDATALEN's can't interoperate.

Wade
>
> Hello,
>
> Looking at the docs for pgsql I have only found stuff on altering a table
> for default and renaming a column but nothing on changing the size. I want
> to increase the size of a field from 2048 to 4096.
>
> What is the syntax for this?
>
> Thanks,
> J
>
>

On Wed, Nov 01, 2000 at 12:43:45PM -0500, J. Atwood wrote:
> Hello,
>
> Looking at the docs for pgsql I have only found stuff on altering a table
> for default and renaming a column but nothing on changing the size. I want
> to increase the size of a field from 2048 to 4096.
>
> What is the syntax for this?

You mean, e.g., a field declared varchar(2048) and now you'd like it to
be varchar(4096)?

Short answer: you can't.

Long answer: create a new table with the same columns, except give the
one you want to change its new size. Then copy all the data across
from one table to the other, drop the old table, and rename the new
one to the old one's name. If you have other long fields in that table,
beware of the 8k limit on the total length of a row - see my post of
ten minutes ago...

Richard

On Wed, 1 Nov 2000, Richard Poole wrote:

> Long answer: create a new table with the same columns, except give the
> one you want to change its new size. Then copy all the data across

I just did it (for another reason)

To alter a table (remove, or change columns)

     # Copy the data
     CREATE TABLE temp AS SELECT * FROM distributors;

     # Drop and recreate the table
     DROP TABLE distributors;
     CREATE TABLE distributors (did      DECIMAL(3)  DEFAULT 1,
                                name     VARCHAR(40) NOT NULL);

     # Get back the data
     INSERT INTO distributors SELECT * FROM temp;

     # Drop the temporary table.
     DROP TABLE temp;

This worked for me:

update pg_attribute set atttypmod = 104 where attname = 'column_name' and
attrelid = (select oid from pg_class where relname = 'tablename');

to set 'columnname' in 'tablename' to a size of 100.

Tamsin


> -----Original Message-----
> From: pgsql-general-owner@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Richard Poole
> Sent: 01 November 2000 18:11
> To: pgsql-general@postgresql.org
> Cc: J. Atwood
> Subject: Re: [GENERAL] Increasing Table Column Size in 7.0 Syntax
>
>
> On Wed, Nov 01, 2000 at 12:43:45PM -0500, J. Atwood wrote:
> > Hello,
> >
> > Looking at the docs for pgsql I have only found stuff on
> altering a table
> > for default and renaming a column but nothing on changing the
> size. I want
> > to increase the size of a field from 2048 to 4096.
> >
> > What is the syntax for this?
>
> You mean, e.g., a field declared varchar(2048) and now you'd like it to
> be varchar(4096)?
>
> Short answer: you can't.
>
> Long answer: create a new table with the same columns, except give the
> one you want to change its new size. Then copy all the data across
> from one table to the other, drop the old table, and rename the new
> one to the old one's name. If you have other long fields in that table,
> beware of the 8k limit on the total length of a row - see my post of
> ten minutes ago...
>
> Richard
>