Re: [SQL] Generic Function
Oh sorry.
I was not clearly.
I've wanted to create a function that suport to select a "parameter variable"
table. Like: return 'select * from $1'.
The Postgresql does not suport this sql function becouse the $1 variable is
considerate as a table... the Postgresql return an error like: The table "$1"
doesn't exist.
Then there is no way to construct this function as SQL function, but I could
make it in PL/TCL function and execute normaly look:
set search_path to public;
create table tb1(vl text);
insert into tb1 values ('aaa');
create table tb2(vl text);
insert into tb2 values ('bbb');
CREATE or REPLACE FUNCTION select_table(text, text, text) returns text as
$$
set schema_name $1;
set table_name $2;
set field_name $3;
set select_query "select $field_name AS select_table from ";
set point ".";
spi_exec $select_query$schema_name$point$table_name;
return $select_table;
$$ language 'pltcl';
Then:
SELECT select_table('public','tb1','vl');
SELECT select_table('public','tb2','vl');
The spi_exec execute the query as a variable ($select_query$...) and
the return
of this query (select $field_name AS select_table) will be the variable
"select_table" for the pl/tcl function. Then I return this variable (return
$select_table).
Is it right?! Is there a better way to make it?
The Pl/Pgsql can built this function? And the SQL Standard?
Thanks...
Quoting George Weaver <[EMAIL PROTECTED]>:
- Original Message -
From: <[EMAIL PROTECTED]>
To:
Sent: Monday, March 14, 2005 12:15 PM
Subject: [SQL] Generic Function
Hi,
Can I built a generic function like:
CREATE FUNCTION f (text) RETURNS TEXT as
$$
return 'select * from $1';
$$
...
If you show us what you've tried and the results you received we may be able
to help more.
...
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Generic Function
Look in the plpgsql docs on EXECUTE. But for something that simple, why don't you just generate a query ? ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] How to force subquery scan?
... > > Does this do better: > > SELECT > prodid, > avg(prodcost) as average_cost, > sum(prodcost * salesamount) as cost_total > FROM ( > SELECT > p.product_id as prodid, > product_cost(s.product_id, s.date) as prodcost, > s.amount as salesamount > FROM products p > LEFT JOIN sales s ON s.date between '2004-01-01' and '2005-01-01' > ) q > GROUP BY prodid; > Sorry, couldn't test it as my query wasn't actual query. I just made up something for the list. But I made some tests with similar queries and your solution doesn't help. Subquery scan is not forced, as your query can be easily translated into single flat query returning the same results. It seems that subquery scan is only used, when the query can not be translated into single flat query. Which is mostly good, I assume. The problem is, that if SELECT list of subquery contains function call, and the result of this function is used in multiple places in outer query, then the function is invoked multiple times (per row). This can get expensive with slow function and big queries. Tambet ---(end of broadcast)--- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [SQL] Parameterized views proposition
> > "Tambet Matiisen" <[EMAIL PROTECTED]> writes: > > How tough it would be to implement > > > CREATE VIEW xxx PARAMETERS (yyy) AS zzz; > > > as > > > CREATE TYPE xxx; > > CREATE FUNCTION xxx(yyy) RETURNING SETOF xxx LANGUAGE sql AS 'zzz'; > > What's the point? It'd be nonstandard anyway, so just use > the function. > > regards, tom lane > 1. Cleaner syntax. 2. No possible inconsistencies between return type and query result. 3. No strange quoting, like '' and $$. 4. Just to save some keystrokes. Another issue, that has to be handled separately: 5. Show up in EXPLAIN as normal query, not function scan. About standards - maybe it's time to set some standard? :) Now seriously, maybe it's really not right to call it a parameterized view. Maybe instead CREATE FUNCTION syntax could be improved somehow, so that you don't have to CREATE TYPE separately. Tambet ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How to force subquery scan?
"Tambet Matiisen" <[EMAIL PROTECTED]> writes: > It seems that subquery scan is only used, when the query can not be > translated into single flat query. Which is mostly good, I assume. The planner thinks so anyway ;-) If you're desperate you can put in an optimization fence, for instance LIMIT or OFFSET. SELECT * FROM (SELECT ... OFFSET 0) ss; In principle the planner could figure out that this offset is a no-op, throw it away, and then flatten the query. But it doesn't at the moment, and I doubt we'll teach it to do so in the future. regards, tom lane ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
[SQL] PGCrypto with Integers and DATE types -- how to?
Hello, How do you encrypt() & decrypt() data of types INT4 or DATE? The PGCrypto methods encrypt() and decrypt() each take BYTEA as input: i.e., encrypt( data::bytea, key::bytea, type::text) decrypt( data::bytea, key::bytea, type::text) So how do you convert INT4 and DATE data into BYTEA so that they can be passed as input into the PGCrypto methods encrypt() & decrypt()? Thank you, Michael Moran ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] How does the planner treat a table function.
If you are interested in performance things, you may use the EXPLAIN command: explain select * from "GetLanguages"() where "Id" > 44 vs. explain select * from "Languages" where "Id">44; However the explain stops at the point a function is invoked. This may mean that (as you asked) a select over a table function produces a temprary table and thus costs much ressources if the table is big. |-Original Message- |From: KÖPFERL Robert |Sent: Montag, 14. März 2005 12:26 |To: pgsql-sql@postgresql.org |Subject: [SQL] How does the planner treat a table function. | | | |Hi, |we have got some tables (uw?) and functions. One function is |defined like | |get_abc(): |SELECT a,b,c from table_x; | |What happens if I query something like |SELECT a,b from get_abc() where a=5; |while table_x is rather big? | | |Will PSQL at first query all records of table_x and then apply |a where a=5 |OR |will PSQL integrate it to a shorter query? | | |(In case there will be of course an intex over a to prevent sequential |search) | |---(end of |broadcast)--- |TIP 9: the planner will ignore your desire to choose an index |scan if your | joining column's datatypes do not match | ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
