Hi, On Fri, Apr 24, 2015 at 12:22 AM, Akshita Jha <[email protected]> wrote:
> Hi, > > On Thu, Apr 23, 2015 at 8:07 PM, Andreas Tille <[email protected]> wrote: > >> Hi, >> >> However, to save my idea to avoid >> code duplication what about a helper returning a table >> >> source text, >> bibentry text >> >> and use this helper in both functions that just return the bibentries. >> I'm just afraid that we might change anything later and will forget to >> change in both functions (been there to much times ;-)). >> >> > I was thinking we could modify generate_bibtex.py to: > > # create BibTeX file > bf = open(self.bibtexfile, 'w') > > bibentry = "(SELECT DISTINCT source, package, rank FROM bibref) p " > if self.all_ref == 1: > query = "SELECT * FROM bibtex(bibentry)" > else: > bibentry += "INNER JOIN sources s ON s.source = p.source " > query = "SELECT * FROM bibtex(bibentry)" > > cur.execute(query) > for row in cur.fetchall(): > print >>bf, row[0] > > bf.close() > > Thus, upgrade_bibtex.sql can take a parameter: > > CREATE OR REPLACE FUNCTION bibtex (text) > RETURNS SETOF TEXT LANGUAGE SQL > AS $$ > SELECT DISTINCT > CASE > .......... > .......... > .......... > FROM $1 > LEFT OUTER JOIN bibref bibkey ....... > ........ > ....... > I don't think the above solution will work as $1 is just a string and not a table. Below is another approach : File: generate_bibtex.py: # create BibTeX file bf = open(self.bibtexfile, 'w') if self.all_ref == 1: bibtable = "SELECT * FROM bibentry(bibref)" query = "SELECT * FROM bibtex(bibtable)" else: bibtable = "SELECT * FROM bibentry(bibref, sources)" query = "SELECT * FROM bibtex(bibtable)" cur.execute(query) for row in cur.fetchall(): print >>bf, row[0] bf.close() File: upgrade_bibtex.sql CREATE OR REPLACE FUNCTION bibentry(bibref text, sources text DEFAULT NULL) RETURNS TABLE(source text, rank text, package text) LANGUAGE SQL AS $$ IF sources IS NULL THEN BEGIN SELECT source, rank, package FROM bibref p; ELSE SELECT source, rank, package FROM bibref p INNER JOIN sources s ON s.source = p.source; END IF; $$; consequently bibtex() changes to: CREATE OR REPLACE FUNCTION bibtex (bibtable text) RETURNS SETOF TEXT LANGUAGE SQL AS $$ SELECT DISTINCT CASE ........ ........ ........ FROM bibtable p LEFT OUTER JOIN ....... ....... ....... What do you think of this ? Anyway I can test it ? -- Regards, Akshita Jha
