[SQL] calling a function within a view causes problems doing a pg_dumpall
Hello I need to transfer a db form one to another server. I wanted to do this with pg_dumpall and psql -e I isolated the problem in a view. When I call a self defined function (buildstring) the problem occurs and when I remove this call pg_dumpall works. Calling the view with select * from viewrzumfang works with and without the function call. I use postgresql 7.0 on SUSE Linux Function : CREATE FUNCTION buildString(bpchar) RETURNS text AS ' DECLARE list text; rec record; BEGIN list := ; FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE z_u_typ = $1; list := list || text(rec.z_u_umfang) || '',''; END LOOP; RETURN list; END; ' LANGUAGE 'plpgsql'; View that is not working : SELECT rotation.r_code, rotation.r_name, rotation.r_z_typ, (SELECT zylinder_typen.z_laenge FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_laenge, (SELECT zylinder_typen.z_blaenge FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_blaenge, (SELECT zylinder_typen.z_alaenge_l FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_alaenge_l, (SELECT zylinder_typen.z_alaenge_r FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_alaenge_r, (SELECT rtrim(buildstring(rotation.r_z_typ), ','::text) AS rtrim) AS r_z_umfang FROM rotation; View that is working : SELECT rotation.r_code, rotation.r_name, rotation.r_z_typ, (SELECT zylinder_typen.z_laenge FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_laenge, (SELECT zylinder_typen.z_blaenge FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_blaenge, (SELECT zylinder_typen.z_alaenge_l FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_alaenge_l, (SELECT zylinder_typen.z_alaenge_r FROM zylinder_typen WHERE (zylinder_typen.z_typ = rotation.r_z_typ)) AS r_z_alaenge_r, FROM rotation; Thanks for any help ... jr PFISTER + PARTNER, SYSTEM - ENGINEERING AG Juerg Rietmann Grundstrasse 22a 6343 Rotkreuz Switzerland phone: +4141 790 4040 fax: +4141 790 2545 mobile: +4179 211 0315 ---(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
[SQL] Re: Need help with search-and-replace
There are oh-so-many ways, as I am sure people will tell you. regular
expressions are the most wonderful things for such a task. I am comfortable
with tcl, so I would read the file into a tcl variable and use 'regsub -all
{\t700:00:00} $instring {} outstring'.
There are unbelievably simple, unvbelievably fast ways to do this in one line
from the shell using sed, but I don't speak sed. I suspect someone will hook
you up with some basic sed.
Try this in Windows. Visual Basic can use regular expressions, but you have to
instantiate a regular expression object, then execute one of it's methods to do
anything. Ugh.
Ian
Josh Berkus wrote:
> Folks,
>
> I need to strip certain columns out of my pgdump file. However, I
> can't figure out how to use any Unix-based tool to search-and-replace a
> specific value which includes a tab character (e.g. replace "{TAB}7
> 00:00:00" with "" to eliminate the column).
>
> RIght now, I'm copying the file to a Win32 machine and using MS Word
> for the search-and-replace, but I'm sure there's got to be a better way
> ... *without* learning VI or Emacs. Help?
>
> -Josh
>
> __AGLIO DATABASE SOLUTIONS___
>Josh Berkus
> Complete information technology [EMAIL PROTECTED]
>and data management solutions (415) 565-7293
> for law firms, small businessesfax 621-2533
> and non-profit organizations. San Francisco
>
> ---(end of broadcast)---
> TIP 4: Don't 'kill -9' the postmaster
---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [SQL] calling a function within a view causes problems doing a pg_dumpall
Juerg, > I need to transfer a db form one to another server. I wanted to do > this > with pg_dumpall and psql -e Have you tried just using pg_dump instead of pg_dumpall? I understand that there were a few bugs in pg_dumpall, and pg_dump will mean less data to transfer. > CREATE FUNCTION buildString(bpchar) RETURNS text AS ' > DECLARE > list text; > rec record; > BEGIN > list := ; > FOR rec IN SELECT z_u_umfang FROM zylinder_umfang WHERE > z_u_typ > = $1; > list := list || text(rec.z_u_umfang) || '',''; > END LOOP; > RETURN list; > END; > ' LANGUAGE 'plpgsql'; It would be nice to see the errors you're getting when you try to restore. Could it be as simple as the dump file trying to create the view before the function? Or plpgsql not being defined as a language on the target server? BTW, there is a better-performance way to do the same thing you're doing with that function. Please browse the list archives with a search for 'catenate' -- you'll find a discussion of custom aggregates which concatinate strings. -Josh __AGLIO DATABASE SOLUTIONS___ Josh Berkus Complete information technology [EMAIL PROTECTED] and data management solutions (415) 565-7293 for law firms, small businessesfax 621-2533 and non-profit organizations. San Francisco ---(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
[SQL] OFFTOPIC: search and replace with unix tools
> "Josh" == Josh Berkus <[EMAIL PROTECTED]> writes:
Josh> Folks, I need to strip certain columns out of my pgdump
Josh> file. However, I can't figure out how to use any Unix-based
Josh> tool to search-and-replace a specific value which includes a
Josh> tab character (e.g. replace "{TAB}7 00:00:00" with "" to
Josh> eliminate the column).
Unix lives by the shell pipe. Set "exit on error", to avoid data loss
in case of "filesystem full", proceed by using "tr" to translate
single characters within the file to something more easily replacable,
do the replace with "sed", translate back using "tr", move over old
file, done:
---
#!/bin/bash
set -e -x
cat "$*" | \
tr '\t' '§' | \
sed -e 's/§7 00:00:00//g' | \
tr '§' '\t' | \
cat > x.$$
mv x.$$ "*"
---
(please don't kill me for the two "cat" operators, they serve no
purpose besides legibility).
so long,
Oliver
---(end of broadcast)---
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
Re: [SQL] RI permission problem
Peter Eisentraut wrote: Kyle writes: > Peter Eisentraut wrote: > > > Kyle writes: > > > > > Is there a way to get this to work without granting update to table b? > > > > Update to 7.1.] > > I'm on 7.1. Should an RI trigger under 7.1 run as the DBA or as the current > user? Okay, we missed a few cases. Try the attached patch. OK, here's another similar one. Should this work? (sorry there's really a little more here than you absolutely need, but it demonstrates the problem) drop view atab_v1; drop view atab_v2; drop view atab_v3; drop view atab_v4; drop table atab; drop function func_atab (); drop function func_v1 (); drop function func_v2 (); create table atab ( f1 int4 ); insert into atab (f1) values (1); insert into atab (f1) values (2); insert into atab (f1) values (3); create view atab_v1 as select * from atab; create view atab_v2 as select * from atab; create function func_atab () returns numeric as ' select sum(f1) from atab; ' language 'sql'; create function func_v1 () returns numeric as ' select sum(f1) from atab_v1; ' language 'sql'; create function func_v2 () returns numeric as ' select sum(f1) from atab_v2; ' language 'sql'; create view atab_v3 as select *,func_v1() from atab_v2; create view atab_v4 as select *,func_atab() from atab_v2; grant select on atab_v2 to kyle; grant select on atab_v3 to kyle; grant select on atab_v4 to kyle; Now as user Kyle, try to select from atab_v3 or atab_v4. Both give permission denied because no explicit permission is given to the view/table underlying the summing function. Shouldn't the select access to the view trickle down to subordinate select functions? Kyle begin:vcard n:Bateman;Kyle x-mozilla-html:FALSE org:Action Target Inc. adr:;; version:2.1 email;internet:[EMAIL PROTECTED] title:President x-mozilla-cpt:;0 fn:Kyle Bateman end:vcard ---(end of broadcast)--- TIP 5: Have you checked our extensive FAQ? http://www.postgresql.org/users-lounge/docs/faq.html
Re: [SQL] Re: Need help with search-and-replace
I am sure someone already sent this reply and I missed it.
Anyway, if I understand the original problem correctly, you want to
find instances of "\t\t00:00:00" and "\t\t\t\t\t\t\t00:00:00", etc. and
remove them.
I hope this is generic enough so you can change it to fit your needs:
echo "Start c 00:00:00crap here." | sed "s/\([^ ]*\)[
]\+[0-9][0-9]:[0-9][0-9]:[0-9][0-9]\(.*\)/\1\2/g"
This will find an instance of "nn:nn:nn" only when preceded by more than one
tab.
Perl is easier to read, so here is a perlish version:
echo "Start c 00:00:00crap here." | {perlish}
"s/([^\t]*)[\t]+\d\d:\d\d:\d\d(.*)/$1$2/g"
Troy
>
> There are oh-so-many ways, as I am sure people will tell you. regular
> expressions are the most wonderful things for such a task. I am comfortable
> with tcl, so I would read the file into a tcl variable and use 'regsub -all
> {\t700:00:00} $instring {} outstring'.
>
> There are unbelievably simple, unvbelievably fast ways to do this in one line
> from the shell using sed, but I don't speak sed. I suspect someone will hook
> you up with some basic sed.
>
> Try this in Windows. Visual Basic can use regular expressions, but you have to
> instantiate a regular expression object, then execute one of it's methods to do
> anything. Ugh.
>
> Ian
>
> Josh Berkus wrote:
>
> > Folks,
> >
> > I need to strip certain columns out of my pgdump file. However, I
> > can't figure out how to use any Unix-based tool to search-and-replace a
> > specific value which includes a tab character (e.g. replace "{TAB}7
> > 00:00:00" with "" to eliminate the column).
> >
> > RIght now, I'm copying the file to a Win32 machine and using MS Word
> > for the search-and-replace, but I'm sure there's got to be a better way
> > ... *without* learning VI or Emacs. Help?
> >
> > -Josh
> >
> > __AGLIO DATABASE SOLUTIONS___
> >Josh Berkus
> > Complete information technology [EMAIL PROTECTED]
> >and data management solutions (415) 565-7293
> > for law firms, small businessesfax 621-2533
> > and non-profit organizations. San Francisco
> >
> > ---(end of broadcast)---
> > TIP 4: Don't 'kill -9' the postmaster
>
>
> ---(end of broadcast)---
> TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
>
---(end of broadcast)---
TIP 6: Have you searched our list archives?
http://www.postgresql.org/search.mpl
[SQL] General ISA and Foreign Key
I have a problem concerning an University assignment in SQL. I am running PostgreSQL. Below part of the E/R diagram in ASCII art. ( #VisaExploitation ) | //\\ | | // \\ | | | 1|
Re: [SQL] RI permission problem
Kyle writes: > Shouldn't the select access to the view trickle down to subordinate select functions? I would think not. -- Peter Eisentraut [EMAIL PROTECTED] http://funkturm.homeip.net/~peter ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] General ISA and Foreign Key
BOUCHPAN-LERUST-JUERY Lionel wrote: >In SQL I have the following tables > >CREATE TABLE film( >VisaExploitation INTEGER NOT NULL, >DureeTournage INTEGER NOT NULL, >Titre VARCHAR( 50 ), > >PRIMARY KEY ( VisaExploitation ) ); >CREATE TABLE filmHistorique( >NbCostume INTEGER >) INHERITS ( film ); > >create table filmDocumentaire( > ) INHERITS ( film ); > >I have a weak entity: > >CREATE TABLE copie( >NumCopie INTEGER NOT NULL, >VisaExploitation INTEGER NOT NULL, >PRIMARY KEY( VisaExploitation, NumCopie ), >FOREIGN KEY( VisaExploitation ) REFERENCES film ON DELETE CASCADE ); > >The problem is I have to be able to have the constraint on >both 2 and 3 and I can't figure how to implement this. You can't do this at present, because there is no support for a foreign key constraint on an inheritance hierarchy (it is a major defect with the current implementation of inheritance). Although inheritance is conceptually correct, the lack of implementation suggests an alternative course: "film" should contain a row for every film and "filmHistorique" and "filmDocumentaire" should have foreign key constraints on "film". You could maintain the contents of "film" by triggers on the other two tables. -- Oliver Elphick[EMAIL PROTECTED] Isle of Wight http://www.lfix.co.uk/oliver PGP: 1024R/32B8FAA1: 97 EA 1D 47 72 3F 28 47 6B 7E 39 CC 56 E4 C1 47 GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839 932A 614D 4C34 3E1D 0C1C "Dearly beloved, avenge not yourselves, but rather give place unto wrath. For it is written, Vengeance is mine; I will repay, saith the Lord. Therefore if thine enemy hunger, feed him; if he thirst, give him drink; for in so doing thou shalt heap coals of fire on his head. Be not overcome of evil, but overcome evil with good." Romans 12:19-21 ---(end of broadcast)--- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
