Hi,
On Sat, May 16, 2015 at 04:18:12PM +0530, Akshita Jha wrote:
> > However, there seems to be some other issue:
> >
> > (master) $ LC_ALL=C psql udd < sql/upgrade_bibtex.sql
> > FEHLER: Syntaxfehler bei „IF“
> > LINE 7: IF sources IS NULL THEN
> > ^
>
> I am not sure but it may be because of the missing:
> BEGIN
> IF ...
> ....
> END IF;
> END;
>
> If you have time, can you please check after modifying the function as below:
>
> CREATE OR REPLACE FUNCTION bibentry (bibref text, sources text DEFAULT NULL)
> RETURNS TABLE(source text, rank text, package text) LANGUAGE SQL
> AS $$
> BEGIN
> IF sources IS NULL THEN
> BEGIN
> SELECT DISTINCT source, rank, package FROM bibref p;
> ELSE
> SELECT DISTINCT source, rank, package FROM bibref p INNER JOIN
> sources s ON s.source = p.source;
> END IF;
> END;
> $$;
I added a patch to your original submitted patch (and to be sure my whole
file) which fixes at least FUNCTION bibentry. There were several changes
needed to get it syntactically correct and working:
udd=# select * from bibentry ('bibref');
source | rank | package
------------------------------+------+----------------------------------------
predictprotein | 0 |
seqan-apps | 0 |
mrbayes | 0 |
drawxtl | 0 |
cp2k | 5 |
...
udd=# select count(*) from bibentry ('bibref');
count
-------
553
udd=# select count(*) from bibentry ('bibref', '1');
count
-------
419
> Or will it be better if I send another patch with these modifications
> ? I am not sure whether this is exactly the issue, but we can check
> this once.
I would prefer if you would send tested patches which apply directly.
Do you have access to some UDD instance which enables you this kind of
tests? If not I'll provide you access to blends.debian.net which could
serve as a sandbox for those tests.
> > FEHLER: Relation „bibtable“ existiert nicht
> > LINE 63: ... FROM (SELECT DISTINCT source, package, rank FROM bibtable) ...
> > ^
> >
> > Moreover, I think you wanted do write source to verify the function
> > parameter, right?
> >
>
> Yes. if sources (which is a function parameter) is NULL then
> references from "bibref" table are taken else there is a join with
> "sources" table. This is what I wanted to check.
>
> > Sorry, no time to track this down completely.
>
> Whenever you get some time, can you please check after the minor
> modification suggested above and let me know ?
I did not fixed the remaining syntax error in "FUNCTION bibtex" since
I'm not fully sure what you want to approach. Please tell me if you
have trouble testing the functions on a real database.
Kind regards
Andreas.
--
http://fam-tille.de
diff --git a/sql/upgrade_bibtex.sql b/sql/upgrade_bibtex.sql index ea9ebf8..5fccc92 100644 --- a/sql/upgrade_bibtex.sql +++ b/sql/upgrade_bibtex.sql @@ -3,14 +3,17 @@ ************************************************************************************/ CREATE OR REPLACE FUNCTION bibentry (bibref text, sources text DEFAULT NULL) -RETURNS TABLE(source text, rank text, package text) LANGUAGE SQL +RETURNS TABLE(source text, rank int, package text) LANGUAGE PLPGSQL AS $$ +DECLARE query text; +BEGIN IF sources IS NULL THEN - BEGIN - SELECT DISTINCT source, rank, package FROM bibref p; + query := 'SELECT DISTINCT p.source, p.rank, p.package FROM bibref p'; ELSE - SELECT DISTINCT source, rank, package FROM bibref p INNER JOIN sources s ON s.source = p.source; + query := 'SELECT DISTINCT p.source, p.rank, p.package FROM bibref p INNER JOIN sources s ON s.source = p.source;'; END IF; + RETURN QUERY EXECUTE query ; +END $$;
Attachment:
upgrade_bibtex.sql
Description: application/sql