Обсуждение: Tweak sql result set... ?
I have a problem where I want to tweak a simple select in an "unobtrusive way". Imagine I have the following select statement: "SELECT name FROM customer LIMIT 1" and I get a normal result set from this. But, could I,maybe by defining some other function or similar, change the result set *without* changing the query? Suppose I get the result from the query above, saying: "Peter Peterson". I would (sometimes) like to get the result "<div>Peter Peterson</div>" but I should not have to change the original query. I know I could write "SELECT '<div>' || name || '</div>' as name FROM customer" but then I have altered the original query and I cannot do this since it is supposed to function different in two different situations. Any ideas on how to achieve this? I would like to let the original sql code stay original. I can prepare postgres before executing the sql if this makes it easier to acheive the goal
(anonymous) wrote: > I have a problem where I want to tweak a simple select in an > "unobtrusive way". Imagine I have the following select statement: > "SELECT name FROM customer LIMIT 1" and I get a normal result set from > this. But, could I,maybe by defining some other function or similar, > change the result set *without* changing the query? Suppose I get the > result from the query above, saying: "Peter Peterson". I would > (sometimes) like to get the result "<div>Peter Peterson</div>" but I > should not have to change the original query. > I know I could write "SELECT '<div>' || name || '</div>' as name FROM > customer" but then I have altered the original query and I cannot do > this since it is supposed to function different in two different > situations. > Any ideas on how to achieve this? I would like to let the original sql > code stay original. I can prepare postgres before executing the sql if > this makes it easier to acheive the goal Have a look at CREATE RULE. Tim
On 2009-07-28, Axe <info@axier.se> wrote: > I have a problem where I want to tweak a simple select in an > "unobtrusive way". Imagine I have the following select statement: > "SELECT name FROM customer LIMIT 1" and I get a normal result set from > this. But, could I,maybe by defining some other function or similar, > change the result set *without* changing the query? Suppose I get the > result from the query above, saying: "Peter Peterson". I would > (sometimes) like to get the result "<div>Peter Peterson</div>" but I > should not have to change the original query. > > I know I could write "SELECT '<div>' || name || '</div>' as name FROM > customer" but then I have altered the original query and I cannot do > this since it is supposed to function different in two different > situations. > > Any ideas on how to achieve this? I would like to let the original sql > code stay original. I can prepare postgres before executing the sql if > this makes it easier to acheive the goal put a wrapper round whatever it is you use to send the queries that modifies the returned values.
> > Any ideas on how to achieve this? I would like to let the original sql > > code stay original. I can prepare postgres before executing the sql if > > this makes it easier to acheive the goal > > Have a look at CREATE RULE. > > Tim > Ok, you mean I could create a rule for the table, then I let the script go into my "black box", do the original selects, but get a manipulated result set back, then I drop the rule so that the blackbox of scripts can get the not manipulated result set back? I need to sometimes get the result "<div>output from query</div>" and sometimes not and it would be awesomw to get it with the same query, but maybe by setting a rule or similar. Best from performance view would be to make something more permanent. Is it possible to activate/deactivate a rule? Axe
i think jasen is thinking of manipulating the result set in your programming enviroment not in the database.
btw from the point of "clean programming" it's a bad idea to integrate html-elements directly into your database quereis. you're mixing data layer and design layer.
what do you mean with sometimes?
2009/7/29 Axe <info@axier.se>
Ok, you mean I could create a rule for the table, then I let the
> > Any ideas on how to achieve this? I would like to let the original sql
> > code stay original. I can prepare postgres before executing the sql if
> > this makes it easier to acheive the goal
>
> Have a look at CREATE RULE.
>
> Tim
>
script go into my "black box",
do the original selects, but get a manipulated result set back, then I
drop the rule so
that the blackbox of scripts can get the not manipulated result set
back?
I need to sometimes get the result "<div>output from query</div>" and
sometimes not
and it would be awesomw to get it with the same query, but maybe by
setting
a rule or similar. Best from performance view would be to make
something
more permanent. Is it possible to activate/deactivate a rule?
Axe
--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
--
Mit freundlichen Grüßen
Dizdarevic Jasmin
Sonnenbergstr. 3
6714 Nüziders, AUT
jasmin.dizdarevic@gmail.com
+43 664 411 79 29
(anonymous) wrote: >> > Any ideas on how to achieve this? I would like to let the original sql >> > code stay original. I can prepare postgres before executing the sql if >> > this makes it easier to acheive the goal >> Have a look at CREATE RULE. > Ok, you mean I could create a rule for the table, then I let the > script go into my "black box", > do the original selects, but get a manipulated result set back, then I > drop the rule so > that the blackbox of scripts can get the not manipulated result set > back? No, I meant that you should have a look at CREATE RULE. From a design perspective, I'd probably rename the old table and put an updatable view in its place. > I need to sometimes get the result "<div>output from query</div>" and > sometimes not > and it would be awesomw to get it with the same query, but maybe by > setting > a rule or similar. Best from performance view would be to make > something > more permanent. Is it possible to activate/deactivate a rule? Of course, DROP RULE. Tim
I agree. All clients issue the same sql and deal with it as they will. The psql client for example can format the results in various ways (pset variations etc). Your client(s) need(s) to interpret their identical results differently. Doesn't seem to me to be the job of SQL? Jasmin Dizdarevic wrote: > i think jasen is thinking of manipulating the result set in your > programming enviroment not in the database. > btw from the point of "clean programming" it's a bad idea to integrate > html-elements directly into your database quereis. you're mixing data > layer and design layer. > > what do you mean with sometimes? > 2009/7/29 Axe <info@axier.se <mailto:info@axier.se>> > > > > > Any ideas on how to achieve this? I would like to let the > original sql > > > code stay original. I can prepare postgres before executing > the sql if > > > this makes it easier to acheive the goal > > > > Have a look at CREATE RULE. > > > > Tim > > > Ok, you mean I could create a rule for the table, then I let the > script go into my "black box", > do the original selects, but get a manipulated result set back, then I > drop the rule so > that the blackbox of scripts can get the not manipulated result set > back? > > I need to sometimes get the result "<div>output from query</div>" and > sometimes not > and it would be awesomw to get it with the same query, but maybe by > setting > a rule or similar. Best from performance view would be to make > something > more permanent. Is it possible to activate/deactivate a rule? > > Axe > > -- > Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org > <mailto:pgsql-sql@postgresql.org>) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql > > > > > -- > Mit freundlichen Grüßen > > Dizdarevic Jasmin > Sonnenbergstr. 3 > 6714 Nüziders, AUT > > jasmin.dizdarevic@gmail.com <mailto:jasmin.dizdarevic@gmail.com> > +43 664 411 79 29 >
> i think jasen is thinking of manipulating the result set in your programming > enviroment not in the database. > btw from the point of "clean programming" it's a bad idea to integrate > html-elements directly into your database quereis. you're mixing data layer > and design layer. > > what do you mean with sometimes? > 2009/7/29 Axe <i...@axier.se> > I am aware of this "clean programming" and this is a part of why I am trying to keep my blackbox of script intact since I want to keep a good standard in those scripts. Why I need it "sometimes" is beacuse I want to use the set of production scripts for test purposes. "Intact" and when I am doing some tests, I would benefit from manipulating the result set I am getting out from postgres for testing purposes by just prepend my test-workbench on top of the production script. / Axe
It seems not trivial to acheive what I wanted so I have decided to move on to get a grip of the smarty object that is also around but after the blackbox of scripts has run instead of before. This obejct can be parsed and is generic enough for my purposes. Thanks for your time and suggestions. / Axe