Postgres Pro
Downloads
Home   >   mailing lists

Thread: storage procedures

storage procedures

From
Nelio Alves Pereira Filho
Date:
I have two questions:

1) How faster are storage procedures from normal queries?
2) Can I define a storage procedure that returns a table? Like,
"SELECT * FROM table"


Thanks


Nelio Pereira
IFX Networks
Brazil

RE: storage procedures

From
Roger Wernersson
Date:
"2" would be a view.

A view can calculate almost any value for any column for any number of rows.

IMHO

/Roger

-----Original Message-----
From: Nelio Alves Pereira Filho [mailto:nelio@ifx.com.br]
Sent: Thursday, November 09, 2000 6:53 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] storage procedures


I have two questions:

1) How faster are storage procedures from normal queries?
2) Can I define a storage procedure that returns a table? Like,
"SELECT * FROM table"


Thanks


Nelio Pereira
IFX Networks
Brazil

Re: storage procedures

From
Nelio Alves Pereira Filho
Date:
But  'views' don't receive parameters, they're just 'static' queries.  I'd like to pass some parameters and then receive the result table.

I'm developping a web base application, and if stored procedures increase performance, I 'd like to use them to retrieve some data.
 

Nelio
 

Roger Wernersson wrote:

"2" would be a view.

A view can calculate almost any value for any column for any number of rows.

IMHO

/Roger

-----Original Message-----
From: Nelio Alves Pereira Filho [mailto:nelio@ifx.com.br]
Sent: Thursday, November 09, 2000 6:53 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] storage procedures

I have two questions:

1) How faster are storage procedures from normal queries?
2) Can I define a storage procedure that returns a table? Like,
"SELECT * FROM table"

Thanks

Nelio Pereira
IFX Networks
Brazil

-- 
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil
 

RE: storage procedures

From
Roger Wernersson
Date:
But views do take "parameters".
 
CREATE VIEW a_view AS
SELECT a, MAX(*) AS b_max
FROM a_table
GROUP BY b;
 
SELECT b_max
FROM a_view
WHERE a = 'hello'; /* parameter */
 
I don't know what problem you want to solve but if you give me an example I will try to show you what I mean.
 
/Roger

-----Original Message-----
From: Nelio Alves Pereira Filho [mailto:nelio@ifx.com.br]
Sent: Thursday, November 09, 2000 8:53 PM
To: Roger Wernersson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] storage procedures

But  'views' don't receive parameters, they're just 'static' queries.  I'd like to pass some parameters and then receive the result table.

I'm developping a web base application, and if stored procedures increase performance, I 'd like to use them to retrieve some data.
 

Nelio
 

Roger Wernersson wrote:

"2" would be a view.

A view can calculate almost any value for any column for any number of rows.

IMHO

/Roger

-----Original Message-----
From: Nelio Alves Pereira Filho [mailto:nelio@ifx.com.br]
Sent: Thursday, November 09, 2000 6:53 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] storage procedures

I have two questions:

1) How faster are storage procedures from normal queries?
2) Can I define a storage procedure that returns a table? Like,
"SELECT * FROM table"

Thanks

Nelio Pereira
IFX Networks
Brazil

-- 
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil
 

Re: storED procedures

From
Nelio Alves Pereira Filho
Date:
Of course!!!!!!! What a stupid question I just made!  Sorry.....

The only thing is that view don't implement views don't implement unions, but I can do union of views instead.

Thanks again!!
 

Roger Wernersson wrote:

But views do take "parameters".CREATE VIEW a_view ASSELECT a, MAX(*) AS b_maxFROM a_tableGROUP BY b;SELECT b_maxFROM a_viewWHERE a = 'hello'; /* parameter */I don't know what problem you want to solve but if you give me an example I will try to show you what I mean./Roger
-----Original Message-----
From: Nelio Alves Pereira Filho [mailto:nelio@ifx.com.br]
Sent: Thursday, November 09, 2000 8:53 PM
To: Roger Wernersson
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] storage procedures
 
But  'views' don't receive parameters, they're just 'static' queries.  I'd like to pass some parameters and then receive the result table.

I'm developping a web base application, and if stored procedures increase performance, I 'd like to use them to retrieve some data.
 

Nelio
 

Roger Wernersson wrote:

"2" would be a view.

A view can calculate almost any value for any column for any number of rows.

IMHO

/Roger

-----Original Message-----
From: Nelio Alves Pereira Filho [mailto:nelio@ifx.com.br]
Sent: Thursday, November 09, 2000 6:53 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] storage procedures

I have two questions:

1) How faster are storage procedures from normal queries?
2) Can I define a storage procedure that returns a table? Like,
"SELECT * FROM table"

Thanks

Nelio Pereira
IFX Networks
Brazil

-- 
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil
 
-- 
Nelio Alves Pereira Filho
IFX Networks
Sao Paulo / Brazil
 

Re: storED procedures

From
Tom Lane
Date:
Nelio Alves Pereira Filho <nelio@ifx.com.br> writes:
> The only thing is that view don't implement unions, but I can do union
> of views instead.

Views containing unions work in current sources (7.1-to-be).

BTW, views containing GROUP BY and/or aggregates are pretty flaky in
current releases; be wary when using one.  Also fixed for 7.1.

            regards, tom lane