Thread: 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

(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