functions which return tuples

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

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