Обсуждение: Redirect sequence access to different schema

Поиск
Список
Период
Сортировка

Redirect sequence access to different schema

От
Magnus Reftel
Дата:
Hi all,

I'm trying to inject some behavior via rules between an application and a table schema, preferably without modifying
eitherof them. Using views, I'm able to have a query that is run with one schema as the search_path to actually run
againsta table in a different schema. Is that also possible for sequences somehow? I tried creating a view like so: 

create view myseq as select * from other_schema.foo_id_seq;

but when I run "select nextval('myseq');" I get an error saying that myseq "is not a sequence". What other options are
there?

Best Regards
Magnus Reftel


Re: Redirect sequence access to different schema

От
Joe Conway
Дата:
On 07/25/2010 12:01 PM, Magnus Reftel wrote:
> create view myseq as select * from other_schema.foo_id_seq;
>
> but when I run "select nextval('myseq');" I get an error saying that
> myseq "is not a sequence". What other options are there?

It isn't clear (to me, at least) what you are trying to accomplish, but
does this do what you want?

create schema other_schema;
create SEQUENCE other_schema.foo_id_seq;

select current_schema;
 current_schema
----------------
 public
(1 row)

select nextval('other_schema.foo_id_seq');
 nextval
---------
       1
(1 row)

select nextval('other_schema.foo_id_seq');
 nextval
---------
       2
(1 row)

Joe

--
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support


Вложения

Re: Redirect sequence access to different schema

От
Magnus Reftel
Дата:
On Jul 25, 2010, at 23:13 , Joe Conway wrote:
> On 07/25/2010 12:01 PM, Magnus Reftel wrote:
>> create view myseq as select * from other_schema.foo_id_seq;
>>
>> but when I run "select nextval('myseq');" I get an error saying that
>> myseq "is not a sequence". What other options are there?
>
> It isn't clear (to me, at least) what you are trying to accomplish, but
> does this do what you want?

Thanks for the reply! Sorry for not being clear. What I'm after is being able to have some code run on the database
withouthaving to modify the application or its database schema. The way I'm trying to achieve this is by setting it up
toaccess a different schema than it usually would, and have that schema act as a proxy for the real schema using views
andrules that perform the alterations I want. It works fine for tables, but I had trouble with getting ti work with
sequences.

One solution I came up with is to not try to emulate the sequence, but the functions accessing the sequence, as in:

alter function currval(regclass) rename to real_currval;
create function inject.currval(unknown) returns bigint as 'select real_currval(''actual.'' || CAST($1 as text));'
languagesql security definer; 

Best Regards
Magnus Reftel