Re: [SQL] Autovaccum

[Matthew T. O'Connor]

Ezequias Rodrigues da Rocha wrote:

It is possible to set this up on PgAdmin ?


I don't know.


I need to shutdown the postgresql service to change this parameters ?


No, you can just modify the params in postgresql.conf and HUP the server 
so that it re-reads the config file, no restart required.




---(end of broadcast)---
TIP 4: Have you searched our list archives?

  http://archives.postgresql.org

[SQL] Finding all References to a Primary Key

[Matthew T. O'Connor]

I'm sorry if this is has been discussed, but I tried to find the answer 
in the archives and failed, so...


How do I find all the rows in other tables that reference a specific row 
in another table?  I'm only trying to find rows that are in tables where 
there is a Foreign Key referencing the primary key of the table in question.


Example:
Table People has a primary key of people_id
There are say 20 tables that have foreign keys referencing people.people_id
How do I find all the rows in all of those 20 tables that reference a 
particular person in the people table?



Thank you in advance,

Matthew O'Connor

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] Joining with result of a plpgsql function

[Matthew T. O'Connor]

Hello,

I have a pl/pgsql function, defined as:

CREATE FUNCTION tms.get_tms_summary(id integer)
  RETURNS tms.tms_summary

get_tms_summary returns a composite type, tms_summary, which is 
comprised of several numerics.


What I would like to do is something like:

select f.id, f.name, tms.get_tms_summary(f.id) from foo f;

However this returns only three columns, the third of which is the 
entire complex data type in one column.


I can do: select * from tms.get_tms_summary(99);

But I would really like to be able to combine it with other data and get 
a result set that looked like:


f.id, f.name, tms_summary.col1, tms_summary.col2 ...


Any thoughts or suggestions?


Thank you,

Matthew O'Connor


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] Joining with result of a plpgsql function

[Matthew T. O'Connor]

Stephan Szabo wrote:

On Wed, 7 May 2008, Matthew T. O'Connor wrote:
  

But I would really like to be able to combine it with other data and get
a result set that looked like:

f.id, f.name, tms_summary.col1, tms_summary.col2 ...



Well I think
 select f.id, f.name, (tms.get_tms_summary(f.id)).* from foo f;
would expand it out into separate columns, but I think that might also
call it multiple times.  You might have better luck combining that with a
subquery like
 select id, name, (summary).col1, (summary).col2, ... from
  (select id, name, tms.get_tms_summary(f.id) as summary from foo) f;


Ah, I knew there was an easy way to do it, I totally forgot / missed / 
didn't know about the (composite type).* syntax.


Thank you!


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] When was my database created

[Matthew T. O'Connor]

Hopefully this is an easy one and sorry if I should have found this in 
the docs somewhere but a cursory glance didn't turn anything up.


How do I tell how old my database is, that is, when was create db for 
this database done?


Thank you,

Matthew O'Connor

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

[SQL] plpgsql setof help

[Matthew T. O'Connor]

Hello, I'm trying to write a pl/pgsql function that takes in a column 
(setof?) of text values and returns an english language list, for example:


if "select towns from towns_table;" retuned
town1
town2
town3

I want the following:
select column_to_english_list( select towns from towns_table );

to return:
'town1, town2 and town3'

In order to do this, I think I would have to create a pl/pgsql function 
that accpts a setof text argument, but I'm not sure that's allowed.  
Anyone care to comment?


Thank you,

Matthew O'Connor


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql