CREATE VIEW (dynamically)

CREATE VIEW (dynamically)

Post by Christoph Hall » Sat, 16 Nov 2002 12:21:28



Quote:> CREATE FUNCTION "requests_insert_after" () RETURNS opaque AS '
> DECLARE
>       view_name       text;
> BEGIN
>       view_name := ''request_'' || NEW.id;
>       CREATE VIEW view_name AS select * from groups;
>       return NEW;
> END' LANGUAGE 'plpgsql';

> CREATE TRIGGER "requests_insert_after" AFTER INSERT ON "requests"  FOR
EACH ROW
> EXECUTE PROCEDURE "requests_insert_after" ();>>

> This code after an insertion on table "requests" give me this message
:
> <<
> PostgreSQL said: ERROR: parser: parse error at or near "$1"

> Why ?

You should check the documentation of plpgsql for the section
Executing dynamic queries (which also means commands like update, etc.)

e.g. to drop a view within plpgsql you have to code

  EXECUTE ''DROP VIEW '' || quote_ident( view_name) ;

Regards, Christoph

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command

 
 
 

1. Using Views to Create Views with outer joins

Does anyone know if it's possible to create a view from two
other
views using an outer join? So far, it doesn't seem to work. The

result set is the same as an inner join.
I was going crazy trying to figure out what was happening in
my datawindows.
To make things simpler to understand, I created 2 simple tables
with 3 columns each. I created a view from these tables, with
an
outer join and the result set was as expected.
I then created 2 views, one from each of my original 2 tables
that contained all columns from the original tables.
In other words, each view was identical to the table
it was derived from.
When I then created a view from these views with an OJ, the
result set was not correct.
Is this technically not possible, due to the way views work, or
have I discovered yet another bug? Using Watcom 4.0

TIA

--bob

2. What to do for 10Mbps-to-100Mbps upgrade

3. creating a script from a view which ceates the original view

4. Upgrade to Pocket PC 2003? Is it worth the effort? What is the improvement and why upgrade?

5. viewing dynamically loaded modules

6. HELP: Ethernet connection to a Centris 650 with Farallon expansion card

7. Q: Views and dynamically changing DWs

8. How to change views in windows explorer dynamically

9. Creating zip file dynamically

10. Creating objects dynamically in the WinRunner GUI map

11. Testing dynamically created list

12. How to create script to dynamically click on HTML links in HTML tables ?